Simple SQL WHERE ... IN query does the opposite of what I need 1

[EagleM]

The query is supposed to search for products that have ALL the specified features (1,2,3).

But what this query does is, search for products that have ANY of the specified features (1 or 2 or 3, for example). It will find all products that have feature 1 or feature 2 or feature 3, and it will display the same product every time for every feature.

This is not what I need. I need it to display only products that have ALL three specified features (1,2,3) together.

How can I do this?

Here is my query:

SELECT id, category, company, model, title, description, picture, price, sale_price, available
                    FROM tblProduct
                    INNER JOIN tblFeatureToProduct
                        ON tblProduct.id = tblFeatureToProduct.id_Product
                    INNER JOIN tblFeature
                        ON tblFeatureToProduct.id_Feature = tblFeature.id_Feature
                    WHERE tblFeature.id_Feature IN (1,2,3)
                    ORDER BY id DESC

 

[r937]

select id
     , category
     , company
     , model
     , title
     , description
     , picture
     , price
     , sale_price
     , available
  from tblProduct
inner 
  join ( 
       select id_Product
         from tblFeatureToProduct
        where id_Feature in (1,2,3)
       group
           by id_Product
       having count(distinct id_Feature) = 3
       ) as x
    on x.id_Product = tblProduct.id
order 
    by tblProduct.id desc

r937.com | rudy.ca