CODEWITHSUNDEEP
sqlpostgresql
michaelsnowden
michaelsnowden

Reputation: 6192

Select rows where all in a group are not null postgresql

I have two tables: groups(group_id), member(group_id, name) and I would like to select all the members in groups where everyone in the group has a non-null name. For example, if this is the members table:

group_id|name
1|a
1|b
2|c
2|null
3|null
3|null

then the result of the query should return:

group_id|name
1|a
1|b

I tried running

SELECT * FROM members M1 
WHERE ALL(SELECT M2.name IS NOT NULL FROM members M2)
ORDER BY M1.group_id

but it didn't work.

Upvotes: 6

Views: 7988

Answers (4)

krokodilko
krokodilko

Reputation: 36087

Another solution:

SELECT * FROM Table
WHERE group_id NOT IN (
   SELECT group_id FROM table
   WHERE name IS NULL
)

Upvotes: 1

Erdiansyah
Erdiansyah

Reputation: 101

If you just need to group and display non null value, how if just a simple

SELECT group_id, name FROM members 
group by group_id, name
having name is not null and id = 1;

Upvotes: 1

klin
klin

Reputation: 121494

Use bool_and():

select group_id, name
from members
where group_id in (
    select group_id
    from members
    group by 1
    having bool_and(name is not null)
    );

Upvotes: 10

Cowthulhu
Cowthulhu

Reputation: 528

SELECT 
    *
FROM groups g
INNER JOIN members m
ON g.group_id = m.group_id
WHERE NOT EXISTS (SELECT * FROM members mbr WHERE mbr.name IS NULL AND mbr.group_id = m.group_id)

Essentially, we select all records, except for those where we can find a null name record with the same group ID.

Note that I don't believe this is SARG-able, so if you have a massive database that relies on indexes, this may be a bit on the slow side.

Upvotes: 1

Related Questions