Reputation: 79
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
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
Reputation: 23766
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
)
Upvotes: 0
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