PostgreSQL: Select unique rows where distinct values are in list-CodePudding

Say that I have the following table:

with data as (
select 'John' "name", 'A' "tag", 10 "count"
union all select 'John', 'B', 20
union all select 'Jane', 'A', 30
union all select 'Judith', 'A', 40
union all select 'Judith', 'B', 50
union all select 'Judith', 'C', 60
union all select 'Jason', 'D', 70
)

I know there are a number of distinct tag values, namely (A, B, C, D).

I would like to select the unique names that only have the tag A

I can get close by doing

-- wrong!
select
  distinct("name")
from data
group by "name"
having count(distinct tag) = 1

however, this will include unique names that only have 1 distinct tag, regardless of what tag is it.

I am using PostgreSQL, although having more generic solutions would be great.

CodePudding user response：

You're almost there - you already have groups with one tag, now just test if it is the tag you want:

select
  distinct("name")
from data
group by "name"
having count(distinct tag) = 1 and max(tag)='A'

(Note max could be min as well - SQL just doesn't have single() aggregate function but that's different story.)

CodePudding user response：

You can use not exists here:

select distinct "name" 
from data d 
where "tag" = 'A'
and not exists (
  select * from data d2 
  where d2."name" = d."name" and d2."tag" != d."tag"
);

CodePudding user response：

This is one possible way of solving it:

select
  distinct("name")
from data
where "name" not in (
  -- create list of names we want to exclude
  select distinct name from data where "tag" != 'A'
)

But I don't know if it's the best or most efficient one.