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

Last updated 22 May 2009




Return to the Artful Common Queries page