Select rows that belong to ONLY one category among many

user_11994902 0
Viewed 288

Let's say there is this table colors

id source
1 red
1 green
1 orange
2 red
2 red
3 black
3 green
4 red
5 green

What I want is the list of all id that have as only source the value 'red', so 2 and 4.

To be clear

select distinct id from colors where source = 'red'

would give 1,2 and 4, where 1 has 'green' and 'orange' in addition to 'red', so no.

Here the SQL to create the table

create temp table colors as 
select *
from (values (1, 'red'),(1, 'green'),(1, 'orange'),(2,'red'),(2, 'red'),(3, 'black'),(3,'green'),(4,'red'),(5,'green'))
as t (id,source)

How to query this?

postgresql sql
2 Answers

Use aggregation:

select id
from t
group by id
having count(*) filter (where color = 'red') = count(*);
user_11440350

I managed to find a query, but it seems so complicated and unpractical:

with colors_sint as (
    --This is the list of distinct sources for every id
    select id, source
    from colors
    group by id, source
), only_red as (
    --Here I check which id has only one distinct source
    select id, count(*) as n_sources
    from colors_sint 
    group by id
    having count(*) = 1
) 
--among those id that have only one source, I take only those that have only red as source
select id
from colors_sint
where id in (select id from only_red)
and "source" = 'red'

Maybe someone has better ideas?

user_119949020
Related