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