Show clients with staff assigned and no staff assigned 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
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