Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to write a query to show a client list including whether or not there is a staff member assigned. If I use this query:

SELECT g.name AS group,  cl.name_lastfirst_cs AS client
FROM clients cl, groups g, link_group lg
WHERE lg.zrud_group = ? 
AND lg.zrud_group = g.zzud_group
AND cl.zzud_client = lg.zrud_client
ORDER BY cl.name_lastfirst_cs

It shows all clients in a group although the staff assignment is left out. If I add the staff in:

SELECT g.name AS group,  cl.name_lastfirst_cs AS client, s.staff_name_cs AS Staff
FROM clients cl, groups g, link_group lg, staff s
WHERE lg.zrud_group = 'NEWHAN'
AND lg.zrud_group = g.zzud_group
AND cl.zzud_client = lg.zrud_client
ORDER BY cl.name_lastfirst_cs

It results in a blank result. Can anyone steer me to the correct way to build this?

UPDATE

I believe I have the answer resulting from your help:

List of clients by group No Staff

SELECT g.name AS group,
cl.name_lastfirst_cs AS client
FROM clients cl
JOIN link_group lg
ON cl.zzud_client = lg.zrud_client
JOIN groups g
ON lg.zrud_group = g.zzud_group
WHERE g.name = 'BEAUFT'
ORDER BY cl.name_lastfirst_cs

With Staff

SELECT g.name AS group,
cl.name_lastfirst_cs AS client,
s.staff_name_cs AS Staff
FROM  clients cl
LEFT OUTER JOIN link_group lg
ON cl.zzud_client = lg.zrud_client
LEFT OUTER JOIN staff s
ON cl.zrud_staff = s.zzud_staff
LEFT OUTER JOIN groups g
ON lg.zrud_group = g.zzud_group
WHERE g.name = 'MARTIN'
ORDER BY g.name, cl.name_lastfirst_cs
share|improve this question
    
You should really add the solution you found as an answer, not as an edit to the question. It is ok to mark your own answer as "the accepted answer" if none of the others is suitable. –  Paul White Aug 21 '13 at 12:06

1 Answer 1

up vote 0 down vote accepted

You are using deprecated syntax for joining tables that doesn't allow for OUTER joins.

I'm not 100% sure what you're trying to accomplish because I can't see your data, but it seems to me like you're performing JOINS when you might want LEFT OUTER JOINS.

First thing to do is to rewrite your query using JOIN keywords.

SELECT 
  g.name AS group,  
  cl.name_lastfirst_cs AS client
FROM 
  clients cl
JOIN
  link_group lg
ON
  cl.zzud_client = lg.zrud_client
JOIN
  groups g 
ON 
  lg.zrud_group = g.zzud_group
WHERE 
  lg.zrud_group = ? 
ORDER BY 
  cl.name_lastfirst_cs

Next, try using LEFT OUTER JOINs.

SELECT 
  g.name AS group,  
  cl.name_lastfirst_cs AS client
FROM 
  clients cl
LEFT OUTER JOIN
  link_group lg
ON
  cl.zzud_client = lg.zrud_client
LEFT OUTER JOIN
  groups g 
ON 
  lg.zrud_group = g.zzud_group
WHERE 
  lg.zrud_group = ? 
ORDER BY 
  cl.name_lastfirst_cs

Here is a decent visual representation of the different kinds of JOINS using Venn diagrams: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

share|improve this answer
    
Paul, Thanks so much for getting me closer to the solution. When I added the third LEFT OUTER JOIN for : –  Detox Aug 21 '13 at 4:37
    
Paul, Thanks so much for getting me closer to the solution. When I added the third LEFT OUTER JOIN for : LEFT OUTER JOIN staff s ON lg.zrud.staff = s.zzud_staff It errors on this last join. Can you see what I am missing? –  Detox Aug 21 '13 at 4:45
1  
I believe I have the solution as a result of your help: –  Detox Aug 21 '13 at 10:55
    
PS, it's Keith not Paul. You don't have to use outer joins on all the queries. You can use them selectively when you need them and when you don't? Also, what is the name of the key field that joins the staff table and the link_group table? And what is the exact error? –  efesar Aug 22 '13 at 16:23
    
But it looks like with your update, you got it working. If that's the case, can you mark this answer as correct so I get credit for it? Thanks, bud! –  efesar Aug 22 '13 at 16:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.