Help converting subquery to query with joins

Question

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

Answer 1

When doing a LEFT JOIN, you need to put all conditions of the JOIN into the ON clause.

In your example you get NULL for left joined columns that do not exist, but you then compare them to values again (<> 1) which does not work.

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

BTW: Using aliases (like c in my example) makes reading and writing your queries easier.

Answer 2

When you restrict on a where clause using the columns in a table that's LEFT joined, you are effectively removing the LEFT OUTER part of the join, because you're filtering on columns that have to be there. Try this instead:

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

This does the restriction as part of the join, so nulls can still be used in the larger query.