Help converting subquery to query with joins
- by Tim
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
