Postgresql – query on 2 fields but 1 is a unique value

postgresqlpostgresql-9.6query

postgresql 9.6, windows 7

Here is an example of a database :

fld1 fld2
abc  D123
xyz  D123
abc  D789

how can I select rows with fld1='abc' if/only for fld2 value exists just one time in the database ?
In my example, I only want to select the third row.

Best Answer

I have extended a bit your sample data:

create table tbl (fld1 text, fld2 text);
insert into tbl values
('abc',  'D123'),
('xyz',  'D123'),
('abc',  'D125'),
('abc',  'D128'),
('def',  'D128'),
('abc',  'D789');

You can use EXIST in your WHERE clause with a dataset grouped by fld2:

select
    t1.*
from
    tbl t1
where
    fld1 = 'abc'
    and exists (select 1 
                from tbl t2 
                where t2.fld2 = t1.fld2 
                group by fld2 
                having count(*) = 1);

fld1 | fld2
:--- | :---
abc  | D125
abc  | D789

db<>fiddle here

Or you can use a CTE to get all fld2 rows with a count of 1, and join it with all rows where fld1 = 'abc':

with count1 as
(
    select   fld2
    from     tbl
    group by fld2
    having   count(*) = 1
)
select
    t1.*
from
    tbl t1
join
    count1
    on t1.fld2 = count1.fld2;

db<>fiddle here

Related Question