I'm stuck on a query with a join. The client's site is running mysql4, so a subquery isn't an option. My attempts to rewrite using a join aren't going too well.

I need to select all of the contractors listed in the contractors table who are not in the contractors2label table with a given label ID & county ID. Yet, they might be listed in contractors2label with other label and county IDs.

Table: contractors

cID (primary, autonumber)
company (varchar)
...etc...

Table: contractors2label

cID
labelID
countyID
psID

This query with a subquery works:

SELECT company, contractors.cID
   FROM contractors
   WHERE contractors.complete = 1
   AND contractors.archived = 0
   AND contractors.cID NOT IN (
     SELECT contractors2label.cID FROM contractors2label
        WHERE labelID <> 1 AND countyID <> 1
   )

I thought this query with a join would be the equivalent, but it returns no results. A manual scan of the data shows I should get 34 rows, which is what the subquery above returns.

SELECT company, contractors.cID
   FROM contractors 
   LEFT OUTER JOIN contractors2label ON contractors.cID = contractors2label.cID
   WHERE contractors.complete = 1
   AND contractors.archived = 0
   AND contractors2label.labelID <> 1
   AND contractors2label.countyID <> 1
   AND contractors2label.cID IS NULL