Say my the schema for these two tables is as follows:
x
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
?
Advertisement
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)
.