Intersecting a result set with a table

Question

I have two tables.

The first one is people, it has three columns (id, name, age).

The second one is for people group, it has three columns (group_id, person_id).

First I have a query to get people whose age is greater equal than 18:

SELECT *
FROM people
WHERE age >= 18;

Now I want to get a group which contains ALL this people. How can I do it?

Answer 1

You want all the above 18 years olds in one group? Well, let's count them and see if the numbers match:

select pg.group_id
from people_group pg join
     people p
     on pg.person_id = p.id and p.age >= 18
group by pg.group_id
having count(distinct pg.person_id) = (select count(*) from people where age >= 18);