SQL to find missing linked records

I'm working on a database project that has two tables. One table contains document information, with one record per document. Another table contains parties, the people/companies linked to the documents. There can be many parties for each document. There are two kinds of parties -- party 1 and party 2.

I needed to find all of the documents of a certain type that didn't have any party 1's. To start, I created SQL like this:


SELECT d.document_id
FROM documents d
LEFT OUTER JOIN parties p
  on d.document_id = p.document_id
WHERE p.party_id is null;


This query simply returns all of the document IDs where there is no party linked to each document. This only gets me 1/2 way to my goal, however, because I only want to see the documents that have no linked party 1's. This returns documents that have any parties linked to them, whether party 1 or 2.

To find only those that are missing party 1's, I used the following SQL:


SELECT d.document_id
FROM documents d
LEFT OUTER JOIN parties p
  on d.document_id = p.document_id AND p.party_num = 1
WHERE p.party_id is null


The interesting thing here is that I added conditions you'd normally think to put in the WHERE clause to the condition of the LEFT OUTER JOIN clause. By doing this, the database only tries to link records between the two tables from the type of party I'm looking for -- party_num = 1.

Doug Smith, Senior Developer at Barefoot.