CODEWITHSUNDEEP
sqlpostgresql
Eli
Eli

Reputation: 79

Postgresql SQL exclude query

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.

Upvotes: 2

Views: 6397

Answers (3)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

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

Upvotes: 3

S-Man
S-Man

Reputation: 23766

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
)
  1. Get all names of paid records
  2. Take them and filter all related records

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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
                 );

Upvotes: 1

Related Questions