views:

34

answers:

2

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
+2  A: 

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.

Peter Lang
Wow, that was fast and your query works. Thanks!
skypanther
OK, I shouldn't have jumped so fast. When I plug it into my app it's actually not working.
skypanther
@Tim: Can you describe the problems you are facing?
Peter Lang
My fault...my logic is backward. Your query will give me what I need if I change to ON ( c2.cID = c.cID AND c2.labelID = 1 AND c2.countyID = 1 ) BTW, I think @Nick's answer below will also work, with the <> to = change.
skypanther
@Tim: I agree, Nicks answer should work too. I think you should up-vote it - and you can up-vote mine too, if you like ;-)
Peter Lang
I just created an account here so I could up-vote the answers. Thanks to both of you!
skypanther
+2  A: 

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.

Nick Craver
+1, since you provided the same query I did.
Peter Lang
@Peter - likewise :)
Nick Craver