Artful Common Queries

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;

Last updated 22 May 2009