Oracle SQL exclude specific type multiple rows select with exact two rows

Question

I am trying to write oracle sql to select all emplids from table ABC excluding the emplids with three specific roles. example is as follows -

TABLE1= ABC

EMPLID        ROLE
______________________
111                Apple
111                 Mango
111                 Red_Apple
222                 Apple
222                 Orange
222                 Red_Mango
222                 Banana
333                 Apple
333                 Orange
444                 Apple
444                 Mango
444                 Red_Mango
555                 Grapes
666                 Orange
666                 Grapes
666                 Blueberry

TABLE2 = DETAILS

EMPLID     NAME     EMAIL
__________________________________
111            John         info@email.com
222            Erica        info@email.com
and so on....

Basically, in above example since Apple, Mango, and Red% are the three roles that needs to be excluded. The sql should return EMPLID and NAME for 222,333,555,and 666. It should exclude 111 and 444

I tried creating sub selects but still not working.`enter code here`. Any advice or help is highly appreciated.

Answer 1

Use conditional aggregation:

SELECT t1.EMPLID,
       t1.NAME,
       t1.EMAIL
FROM DETAILS t1
INNER JOIN
(
    SELECT EMPLID
    FROM ABC
    GROUP BY EMPLID
    HAVING SUM(CASE WHEN ROLE = 'Apple' OR ROLE = 'Mango' OR ROLE LIKE 'Red%'
                    THEN 1 ELSE 0 END) < 3
) t2
    ON t1.EMPLID = t2.EMPLID