Skip to content
Advertisement

In a one to many relationship, is it possible to do a query for parent based on if they have certain children without using count and group by?

Say my the schema for these two tables is as follows:

owner
-----
pk owner_id int
owner_name text

possession
-----
pk possession_id int
possession_name text
fk owner_id int

Now, say the the records in these possession and owner tables are as follows:

owner
owner_id | owner_name
---------------------
1 | 'stephen'
2 | 'sarah'

possession
possession_id | possession_name | owner_id
------------------------------------------
1 | 'truck' | 1
2 | 'hammer | 1
3 | 'bottle' | 1
4 | 'truck' | 2
5 | 'island' | 2

In non-sql language: What is the query that could search for owners that have a truck and a hammer? And in sql language: is there a solution for this that does not use count + group by?

Answer

I like to do this with group by and having:

select owner_id
from possession
where possession_name in ('truck', 'hammer')
group by owner_id
having count(distinct possession_name) = 2;

If owner/possession is unique, then use count(*) instead of count(distinct).