CODEWITHSUNDEEP
sqlpostgresql
Dyaksa Hanindito
Dyaksa Hanindito

Reputation: 153

Postgresql - How to get record from the same table that doesn't occur specific data

In PostgreSQL, if i have this kind of record :

|---------------------|------------------|
|      Name           |     Paid         |
|---------------------|------------------|
|        Teddy        |        No        |
|---------------------|------------------|
|        Andy         |        No        |
|---------------------|------------------|
|        Andy         |        Yes       |
|---------------------|------------------|
|        Sarah        |        Yes       |
|---------------------|------------------|
|        Michael      |        No        |
|---------------------|------------------|
|        Michael      |        Yes       |
|---------------------|------------------|

How do i get Names that don't have 'Yes' occurence in the table?

In this example the result should be : Teddy.

Thanks !

Upvotes: 0

Views: 45

Answers (4)

Hambone
Hambone

Reputation: 16377

Another alternative:

select name 
from yourtable
group by name
having count (*) filter (where paid = 'Yes') = 0

This will do at most a single scan of the table, which may be helpful if your table is large.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Assuming the values are only 'Yes' and 'No':

select name
from t
group by name
having min(paid) = max(paid) and min(paid) = 'No';

Upvotes: 0

GMB
GMB

Reputation: 222482

I would use not exists with a correlated subquery:

select t.name, t.paid
from mytable t
where not exists (
    select 1 from mytable t1 where t1.name = t.name and t1.paid = 'Yes'
)

For performance with this query, you want an index on (name, paid).

Upvotes: 1

Joan Lara
Joan Lara

Reputation: 1396

select name
from table
where name not in (
    select name 
    from table 
    where paid = 'Yes'
)

Upvotes: 0

Related Questions