psid mysql_sql-帮助将子查询转换为具有联接的查询

我陷入了联接查询.客户端站点正在运行mysql4,因此不能选择子查询.我使用联接重写的尝试进行得不太顺利.

我需要选择承包商表中列出的所有不在给定标签ID&县ID.但是,它们可能会列在

contractors2label,以及其他标签和县ID.

表：承包商

cID (primary, autonumber)

company (varchar)

…etc…

表：contractors2label

cID

labelID

countyID

psID

此查询与子查询一起工作：

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

)

我认为带有联接的查询将是等效的,但它不会返回任何结果.手动扫描数据显示我应该得到34行,这是上面的子查询返回的结果.

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