Parents with and without children

from the Artful Common Queries page


You have parties and contracts tables. Every contracts row has a contractorpartyID value which references a row in parties, and a clientpartyID value which also references a row in parties. How to list all parties and their contracts, showing blanks as empty strings rather than NULLs?
SELECT parties.partyID, 
       IFNULL(contractorpartyID,'') AS contractor, 
       IFNULL(clientpartyID,'') AS client
FROM parties 
LEFT JOIN contractor_client ON partyID=contractorpartyID
ORDER BY partyID;
+---------+------------+--------+
| partyID | contractor | client |
+---------+------------+--------+
|       1 |            |        |
|       2 | 2          | 1      |
|       3 |            |        |
+---------+------------+--------+

Return to the Artful Common Queries page