I have these columns on my table:
id country
A Italy
A France
A Germany
B Italy
B France
C Italy
C France
I want to get id with contains ONLY Italy and France, so the result will becoming like this:
id
B
C
Thanks for helping me
The easiest way to do this for me is to just aggregate by the id and then assert that the minimum country is France, and the maximum Italy:
SELECT id
FROM yourTable
GROUP BY id
HAVING MIN(country) = 'France' AND MAX(country) = 'Italy' AND
COUNT(DISTINCT country) = 2;
you can try like below by using exists
with cte as
(
select 'A' as id , 'Italy' as country union all
select 'A','France' union all
select 'A','Germany' union all
select 'B','Italy' union all
select 'B','France' union all
select 'C' ,'Italy' union all
select 'C','France'
) select distinct id from cte t1
where exists ( select 1 from cte t2
where t1.id=t2.id
having count(distinct country)=2
)
and country in ('France','Italy')
output
id
B
C
A more general approach in Postgres is to use array for the comparison:
select id
from t
group by id
having array_agg(country order by country) = array['France', 'Italy'];
Here is a db<>fiddle.
Why don't you use this
select distinct(id)
from countries
where country in ('Italy','France');
