MySQL select help

Question

Hi I have a table that looks like this

id : productid : featureid
(1, 1, 16)  
(2, 1, 21)  
(3, 1, 25)  
(4, 2, 16)  
(5, 2, 21)  
(6, 2, 27)  

where featureid is a foreign key to another table.

I need to select products that have both featureids of 16 and 25, in the example this would be productid 1 but not productid 2.

Can someone show me an example of how to format this query?

Answer 1

I didn't understand the last statement about "productid 1 but not productid 2".

I haven't tested this code, but you can do something like this:

SELECT a.* FROM `products` AS a INNER JOIN `products` AS b ON ( a.`productid` = b.`product_id` WHERE `featureid` = 25 ) WHERE a.`featureid` = 16

or

SELECT * FROM `products` WHERE `featureid` = 16 AND `product_id` IN ( SELECT `productid`FROM `products` WHERE `featureid` = 25 )

Answer 2

To get all products which have both features, you can use a sub-query, and then use Group By with Having-clause to only return those products which have both features (assuming that productId, featureId is unique in your table).

Select productId
From (
  Select productId
  From your_table
  Where featureId In ( 16, 25 )
)
Group By productId
Having Count(*) = 2