1

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
0
0

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

7
  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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