CODEWITHSUNDEEP
sqloracle-database
Sourabh
Sourabh

Reputation: 95

Need Oracle sql to list records where for a key, specific value is paired with other values

I have data in a table Tab like this :

DEPT    ROLE
-------------    
100     Sell
101 Admin
102 Admin
102 Staff
103 Admin
103 Staff
103 Sell
104 FRQ
104 Staff
104 Sell
105 Admin
105 INT
105 Sell

I need to list only DEPTs where Admin is appearing with at least one other role but not Admin alone or not any other DEPT where Admin is not present. For Example : 102, 103,105 are desired results but 100,101,104 are filtered out.

Could you please assist me in this ?

Thanks in advance.

Upvotes: 0

Views: 54

Answers (3)

patelb
patelb

Reputation: 2581

here is another way that will give you depts that have more than one role which includes the role admin

SELECT a.DEPT
from TEST a
join (select DEPT from TEST group by DEPT having count(*) > 1) b on a.id = b.id and a.ROLE = 'ADMIN'

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

select DEPT
from T
group by DEPT
having
        count(case when ROLE  = 'Admin' then 1 end) > 0
    and count(case when ROLE <> 'Admin' then 1 end) > 0

You can certainly use an inner join for simple conditions. This is the more general approach that's adaptable if the query needs to change. By eliminating the join(s) it's likely to be faster as well.

Upvotes: 1

Paul
Paul

Reputation: 1167

SELECT DISTINCT YT.DEPT
FROM   YOUR_TABLE YT
       JOIN
         YOUR_TABLE ADM ON ADM.DEPT = YT.DEPT
WHERE  ADM.ROLE = 'Admin'
AND    YT.ROLE != 'Admin'

Basically I used inner join to filter depts having no 'Admin' roles and filtered 'Admin' roles from this resultset.

Upvotes: 1

Related Questions