Postgresql SQL exclude query

Question

I'm relatively new to Postgresql and SQL in general. I've been sitting on this problem for some time and can't figure out how to write the query to get the result I want.

I have this example table

User	Name	Paid
A	aaa	true
B	bbb	true
C	ccc	false
D	aaa	false
E	eee	false

If I execute the query select * from table where Paid is false then I will get

User	Name	Paid
C	ccc	false
D	aaa	false
E	eee	false

From this table, I want to exlude all users who have the same Name as users in the Paid=true set. So in the end I want to have this result

User	Name	Paid
C	ccc	false
E	eee	false

SO basically I want to get all users who have not paid Paid = false and on top of that, exclude all users with the same names as the users who have paid Paid = true.

How do I do this in one query? Thank you in advance.

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2023-11-20 20:10:10Z

3

You can use not exists as below:

Schema:

 CREATE TABLE tableUser ("User" varchar(10), Name varchar(10), Paid boolean);
     
 INSERT INTO tableUser  VALUES('A', 'aaa', 'true');
 INSERT INTO tableUser  VALUES('B', 'bbb', 'true');
 INSERT INTO tableUser  VALUES('C', 'ccc', 'false');
 INSERT INTO tableUser  VALUES('D', 'aaa', 'false');
 INSERT INTO tableUser  VALUES('E', 'eee', 'false');

Query:

 select User, Name, Paid from tableuser t
 where paid is false and
       not exists (select 1
                   from tableuser tb
                   where t.name = tb.name and tb.paid is true );

Output:

user	name	paid
u_519553787	ccc	f
u_519553787	eee	f

db<>fiddle here

edited Nov 20, 2023 at 20:10

answered Mar 15, 2021 at 13:34

Kazi Mohammad Ali Nur Romel

16.2k2 gold badges17 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2021-03-15 13:32:19Z

1

One method uses not exists:

select t.*
from t
where not paid and
      not exists (select 1
                  from t t2
                  where t2.name = t.name and t2.paid
                 );

Of course, the not paid in the outer query is redundant. So, you can just use:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.name = t.name and t2.paid
                 );

answered Mar 15, 2021 at 13:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

S-Man · Accepted Answer · 2021-03-15 13:33:35Z

0

demo:db<>fiddle

You can exclude the names with a WHERE subquery:

SELECT
    *
FROM mytable
WHERE "Name" NOT IN (                               -- 2
    SELECT "Name" FROM mytable WHERE "Paid" = true  -- 1
)

Get all names of paid records
Take them and filter all related records

answered Mar 15, 2021 at 13:33

S-Man

24k9 gold badges51 silver badges78 bronze badges

Collectives™ on Stack Overflow

Postgresql SQL exclude query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related