Select rows where all in a group are not null postgresql

Viewed 5477

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.

4 Answers
Related