[SQL] Sql select where array in column

In my query I use join table category_attributes. Let's assume we have such rows:

category_id|attribute_id
1|1
1|2
1|3


I want to have the query which suites the two following needs. I have a variable (php) of allowed attribute_id's. If the array is subset of attribute_id then category_id should be selected, if not - no results.

First case:

select * from category_attributes where (1,2,3,4) in category_attributes.attribute_id


should give no results.

Second case

select * from category_attributes where (1,2,3) in category_attributes.attribute_id


should give all three rows (see dummy rows at the beginning).

So I would like to have reverse side of what standard SQL in does.

Continue reading...