CODEWITHSUNDEEP
mysql
bikey77
bikey77

Reputation: 6672

mysql - What should I use instead of IN statement?

In the following mysql query I'm trying to select all products that have ALL the features in the parenthesis. I'm using IN that apparently returns products that have feature 1 OR feature 2 etc... What should I use instead of IN?

SELECT *, f.name as feature, p.productcode as productcode, p.name as name 
from product p 
left join productfeatures pf on p.productcode = pf.productcode 
left join features f on pf.featureid = f.id
where f.id in (1, 2, 3) 
group by p.productcode

Upvotes: 0

Views: 73

Answers (1)

juergen d
juergen d

Reputation: 204756

add this to the end

having count(f.id) = 3

That will only result groups that have 3 f.ids and not just 1 or 2.

if you have more that 3 f.ids in your in clause, you need to extend that value. Example:

where f.id in (1, 2, 3, 4, 5) 
group by p.productcode    
having count(f.id) = 5

Upvotes: 4

Related Questions