How to enable entries in which one half of the connection is missing?

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 

Gets a list something like this:

 Alice Accounting Bob Systems Charlie Customer Services, Administration 

Okay bye. Now suppose I delete the entry in staff_units that writes Alice as a member of Accounting. Running this query will then give a list from which Alice is excluded, although she still exists in the staff table:

 Bob Systems Charlie Customer Services, Administration 

Can I customize this SQL so that it continues to return Alice as a result, showing her as unwritten for a unit?

Of course, I could run one request to get a list of employees, and another request for each of them to get the current appointment. But that would mean running n + 1 queries to create a list, where n is the number of employees, and that just spoils me wrong.

Answer 1

Would you use left join

In relation to your statement, it will 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 connection type used is becoming obsolete.

Answer 2

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

So your request 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}