MySQL join problem

Question

Whats wrong with this SQL? It should return results but returns nothing

SELECT `pid` 
  FROM `products` 
  LEFT JOIN `prods_to_features` 
    ON (`ptf_pid` = `pid`) 
 WHERE (   `ptf_id` = '66' 
        OR `ptf_id` = '67'
       ) 
   AND (`ptf_id` = '76')

Is it not possible to have the 2nd where clause for the table that has been used in the left join?

Answer 1

Take a look at your WHERE clause.

You can't have a value be (66 OR 67) AND 76 at the same time. Every row will return false for the expression, so no rows will be returned.

Answer 2

The problem has nothing to do with your join.

You check for

(`ptf_id` = '66' OR `ptf_id` = '67') AND (`ptf_id` = '76')

which is the same as

   (`ptf_id` = '66' AND `ptf_id` = '76' )
OR (`ptf_id` = '67' AND `ptf_id` = '76')

which means ptf_id = 66 = 76 or ptf_id = 67 = 76 which is, well, unlikely.

Answer 3

The problem is that you are asking it to return records from prods_to_features that have two different values for ptf_id (the AND in your Where clause).

I suspect you may mean pid for one or two of your ptf_id in the Where clause. It just depends on what you're trying to do.

Answer 4

Your boolean logic is flawed.

WHERE (ptf_id = 66 or 67) and (ptf_id = 76)

this will never work.

Answer 5

As others have said, the WHERE clause makes no sense.

I'm going to guess that what you're trying to say is you want products that have a feature-76 AND have a feature 66-or-67. You will need two joins for that:

SELECT DISTINCT products.pid
FROM products
JOIN prods_to_features AS ptf1 ON ptf1.ptf_pid=products.pid
JOIN prods_to_features AS ptf2 ON ptf2.ptf_pid=products.pid
WHERE ptf1.ptf_id='76'
AND ptf2.ptf_id IN ('66', '67')

The DISTINCT is to ensure only one copy of a product is returned in the case where a product has both features 66 and 67.