Help convert a subquery to a query with joins

Question

I went in cycles on request with connection. Mysql4 is running on the client site, so a subquery is not an option. My attempts to rewrite using a join are not too good.

I need to select all the contractors listed in the contractor table that are not in the contractor's subscriber table with the label identifier and county identifier. However, they can be listed in contractors2label with other county tags and identifiers.

Table: Contractors

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

Table: contractors2label

IDS
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
   )

, , . , 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

Answer 1

LEFT JOIN JOIN ON.

NULL , , (<> 1), .

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: (, c ) .

Answer 2

where, , LEFT , LEFT OUTER , , . :

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

, NULL.