How to select data from one table that doesn't have any value from another table-CodePudding

I have a problem with my queries (using SQL Developer). This might be simple case but I have troubles with my thinking... so I have two tables GROUP and PERSON. I need to select all the group which don't have any males in them. So the SQL should return to me in this case only GROUP_NAME of Dogs (GROUP_ID = 2) as they have only females in them. I appreciate all the help!

So table1 GROUP:

and table2 PERSON:

CodePudding user response：

Try

SELECT 
    GROUP_NAME
FROM GROUP_TABLE g
WHERE NOT EXISTS
    (SELECT 1 FROM PERSON p WHERE SEX = 'male' AND p.GROUP_ID = g.GROUP_ID)

CodePudding user response：

SELECT G.GROUP_ID,G.GROUP_NAME
FROM GROUP G
WHERE NOT EXISTS
(
  SELECT 1 FROM PERSON P WHERE G.GROUP_ID=P.GROUP_ID AND P.SEX='MALE'
)

CodePudding user response：

select a.group_id
from group a, person b
where a.group_id=b.group_id and
group by a.group_id having COUNT(CASE b.sex WHEN 'male' THEN 1 END)=0 and
                      COUNT(CASE b.sex WHEN 'female' THEN 1 END)>0