How to query only groups that don't have an 'owner'?-CodePudding

I want to query only group_id that don't have a role = 'owner' to delete it then I try to do like this

select group_id from group_members where role != 'owner'

but when I get a group_id, it still has the role 'owner'

ex result:> group_id = g001(but g001 has role 'owner') and g002

it should query just group_id = g002

CodePudding user response：

SELECT G.GROUP_ID 
FROM GROUP_MEMBER AS G
WHERE NOT EXISTS
 (
   SELECT 1 FROM GROUP_MEMBER AS X WHERE G.GROUP_ID=X.GROUP_ID AND X.ROLE='OWNER'
 )

CodePudding user response：

Use where not in.

 select group_id from group_members where group_id NOT IN (select DISTINCT(group_id) from group_members where role == 'owner');