SQL question conjunction

Question

Let's say you have a simple table with two fields: account and item: An account can have multiple items. For example:

Account    Item
11         Apples
11         Pears
11         Crackers
12         Apples
12         Bannanas
12         Cookies
13         Pears
13         Carrots
13         Apples

How would I write a query so that I could select all accounts with some specified set of items. For example, how would I write query so that I would select accounts where the fields are assigned both Apples and Pears (in the example accounts 11 and 13).

Thank you for your help.

Elliott

Answer 1

You could construct a query that will leave only the apples and pears in the set, then group by account and count the distinct items. If it matches the count expected (2 in this case) then the account has the full set.

select account
from tbl
where item in ('apples','pears')
group by account
having count(distinct item) = 2