CODEWITHSUNDEEP
sqlpostgresql
GB 420
GB 420

Reputation: 25

What is the best way to select rows belonging to other IDs which have the exact same rows as an ID in question using SQL?

Suppose I have a database where I keep track of people and their hobbies, and there are two tables: People and Hobbies. Now if there exists a person named Tom from table People with the two hobbies 'fishing' and 'jogging' in table Hobbies, how can I check for other persons who have exactly these two hobbies? I want to exclude people who have, for instance, the hobbies Fishing, Jogging AND Gaming. I have tried the following:

select name 
from people
where name IN( 
select name_hobbyist
from hobby
where hobby IN(
select hobby
from hobby 
where name_hobbyist =(
select name
from people
where name = 'Tom'
) 
)
)
order by name asc

And it returns no rows.

Upvotes: 1

Views: 392

Answers (1)

forpas
forpas

Reputation: 164099

Since you have the names of people in the table hobby you don't need the table people.

You can group by name_hobbyist and use the aggregate function string_agg() in the having clause to apply the condition:

select name_hobbyist 
from hobby
where name_hobbyist <> 'Tom'
group by name_hobbyist 
having string_agg(hobby, ',' order by hobby) = (
  select string_agg(hobby, ',' order by hobby)
  from hobby
  where name_hobbyist = 'Tom'
)

Upvotes: 1

Related Questions