CODEWITHSUNDEEP
sqlsqlitegroup-by
punksta
punksta

Reputation: 2808

Sqlite select column contains each of ids

I have a table 

 CREATE TABLE humans_to_pets (
        human_id   INT  NOT NULL,
        pet_id     INT  NOT NULL
    )

where I am storing pairs what human own what pet (many to many).

Given list of pet_id I need to find all humans who owns each pet from the list.

for instance

human_id |  pet_id
1           11
2           11
2           12
3           13
4           11
4           12

find([11,12]) should return [2, 4]. Only humans 2 and 4 owns both pets 11 and 12.

I am using sqlite 3.31

Upvotes: 0

Views: 37

Answers (1)

GMB
GMB

Reputation: 222472

You can use aggregation, and filter with a having clause:

select human_id
from humans_to_pets
where pet_id in (11, 12)
group by human_id
having count(distinct pet_id) = 2

Assuming that (pet_id, human_id) tuples are unique (which would make sense for this junction table), this can be simplified to not use distinct, which would make the query more efficient:

select human_id
from humans_to_pets
where pet_id in (11, 12)
group by human_id
having count(*) = 2

Upvotes: 1

Related Questions