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.


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:

    tbl t1
    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
    tbl t1
    on t1.fld2 = count1.fld2;

db<>fiddle here

| improve this answer | |
  • I like the second query, it talks to me more than the first. Thx – Leehan Dec 5 '18 at 8:17
  • I'm glad to help. – McNets Dec 5 '18 at 8:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.