CODEWITHSUNDEEP
sqlsqlite
Razgriz231
Razgriz231

Reputation: 105

SQL - Select items where value equals multiple values

Have a very hard time wording this question, but below is a table to illustrate my problem.

Id   itemID   categoryID
1      5         10
2      5         16
3      6         10
4      2         10

If I have a table setup like this, and I want to select "itemID" where categoryID equals 10 AND 16, the result should be itemID 5. A bit more context would be the user has a list of checkboxes that are the categoryID's, and if they select just categoryID 10, then itemID 5, 2 and 6 would appear. If they also select categoryID 16, then only itemID 5 would appear since it has category 10 and 16, where itemID 2 only has category 10.

Upvotes: 2

Views: 4721

Answers (2)

Bohemian
Bohemian

Reputation: 424983

Join the table to itself:

select t1.itemID
from mytable t1
join mytable t2 on t2.itemID = t1.itemID
where t1.categoryID = 10
and t2.categoryID = 16;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

This is an example of a "set-within-sets" subquery. I think the most general way to solve these is using aggregation and a having clause:

select itemID
from t
group by itemId
having sum(case when categoryID = 10 then 1 else 0 end) > 0 and
       sum(case when categoryID = 16 then 1 else 0 end) > 0;

Each condition in the having clause is counting the number of rows that match one category. You can easily see how this would generalize for more categories, or to exclude a category. For instance, if you wants 10 and 16 but not 22:

select itemID
from t
group by itemId
having sum(case when categoryID = 10 then 1 else 0 end) > 0 and
       sum(case when categoryID = 16 then 1 else 0 end) > 0 and
       sum(case when categoryID = 22 then 1 else 0 end) = 0;

Upvotes: 4

Related Questions