How do I include records lacking one half of a join?

Question

Suppose I have a query like this:

SELECT 
    s.staffID as staffID, 
    CONCAT_WS(", ", lname, fname) AS name, 
    GROUP_CONCAT(unit SEPARATOR ", ") AS units 
FROM 
    staff s, 
    units u, 
    staff_units r 
WHERE 
    s.staffID = r.staffID 
    AND u.unitID = r.unitID 
GROUP BY s.staffID 
ORDER BY lname

This gets a list something like this:

Alice    Accounting
Bob      Systems
Charlie  Customer Services, Administration

Okay so far. Now suppose that I delete the entry in staff_units that records Alice as a member of Accounting. Running this query will then yield a list from which Alice is excluded even though she still exists in the staff table:

Bob      Systems
Charlie  Customer Services, Administration

Can I adjust this SQL so that it continues to return Alice as a result, showing her as unassigned to a unit?

I could of course run one query to get a list of staff, and another query for each one of them to get current assignments. But that would mean running n+1 queries to build the list, where n is the number of staff, and that just rubs me wrong.

score 6 · Accepted Answer · edited May 23 '17 at 12:16

6

You would use a LEFT JOIN

Applied to your statement, this would become

SELECT 
    s.staffID as staffID, 
    CONCAT_WS(", ", lname, fname) AS name, 
    GROUP_CONCAT(unit SEPARATOR ", ") AS units 
FROM 
    staff s
    LEFT OUTER JOIN staff_units r ON s.staffID = r.staffID     
    LEFT OUTER JOIN units u ON u.unitID = r.unitID 
GROUP BY s.staffID 
ORDER BY lname

Note that the implicit type of join you are using is becoming deprecated.

edited May 23 '17 at 12:16

Community

1
1

answered Jan 05 '12 at 22:38

Lieven Keersmaekers

57,207
13
112
146

Excellent, that did the trick. Thanks. I'll accept in a few minutes once the cooldown expires. – Will Martin Jan 05 '12 at 22:42
2

@WillMartin - Thank you but do take the note serious. Once you learn to use the explicit join syntax, you will find it much easier to read and maintain. – Lieven Keersmaekers Jan 05 '12 at 22:44
Thanks, I'll invest some time in that. – Will Martin Jan 05 '12 at 23:03

score 1 · Answer 2 · answered Jan 05 '12 at 22:41

Use LEFT OUTER JOIN. http://dev.mysql.com/doc/refman/5.0/en/join.html

So your query will be

SELECT 
    s.staffID as staffID, 
    CONCAT_WS(", ", lname, fname) AS name, 
    GROUP_CONCAT(unit SEPARATOR ", ") AS units 
FROM 
    {staff s LEFT OUTER JOIN  staff_units r ON s.staffID=r.staffID}

How do I include records lacking one half of a join?

2 Answers2