Select Statement Help

[mclamais]

I'm having problems with creating a simple SQL select statement.

The table is as follows
table - products2features
fields - prod_id, feature_id
49 1
49 3
49 4
50 2
50 5
51 1
51 3
51 6

The user on the website selects from various features, and I want to determine which single product matches each feature selected. So let say the user selects feaure_id 1 and 3 and 4, I want to return the prod_id, which in this case is 49.

Here's what I tried,

SELECT prod_id
FROM products2features
WHERE products2features.feature_value_id =1
AND products2features.feature_value_id =3
AND products2features.feature_value_id =4

No results

SELECT prod_id
FROM products2features
WHERE products2features.feature_value_id IN (1,3,4)

Results
49
51

But I only want the prod_id that matches each of the selected features.

It seems like it should be so simple, but I guess it too late for my brain to work.

[r937]

SELECT prod_id
  FROM products2features
 WHERE feature_value_id IN (1,3,4)
GROUP
    BY prod_id
HAVING COUNT(*) = 3

[IBazz]

Does that count make sure that the returned product_id matches on three (all three) values in the IN array?

bazz
liking the new clock

[guido2004]

It does if product/feature is the unique key of the table.

[mclamais]

SELECT prod_id
  FROM products2features
 WHERE feature_value_id IN (1,3,4)
GROUP
    BY prod_id
HAVING COUNT(*) = 3

Thanks so much, it works perfectly.