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.
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)
);
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With