Parties who have contracts with one another

from the Artful Common Queries page


You have a parties table that holds info on peoples' names etc, and a contracts table where each row has clientID and contractorID value pointing at a parties.partyID value--that is, each contracts row points at two parties rows. You want to list the names of all contractors and their clients.
SELECT clientpartyID, 
       pCli.name AS Client, 
       contractorpartyID, 
       pCon.name AS Contractor
FROM contracts
  INNER JOIN parties AS pCli 
    ON contracts.clientpartyID = pCli.partyID
  INNER JOIN parties AS pCon 
    ON contracts.contractorpartyID = pCon.partyID;

Return to the Artful Common Queries page