CODEWITHSUNDEEP
sqlmysql
robjmills
robjmills

Reputation: 18588

MySQL join problem

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?

Upvotes: 0

Views: 105

Answers (5)

bobince
bobince

Reputation: 536339

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.

Upvotes: 4

Timothy
Timothy

Reputation: 2477

Your boolean logic is flawed.

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

this will never work.

Upvotes: 2

Chris
Chris

Reputation: 2991

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.

Upvotes: 2

Peter Lang
Peter Lang

Reputation: 55524

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.

Upvotes: 5

Marcus Adams
Marcus Adams

Reputation: 53830

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.

Upvotes: 7

Related Questions