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
?
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)
.