CODEWITHSUNDEEP
sqloracle-database
Media Player
Media Player

Reputation: 21

How to query to find if all rows in one table match with a set of rows in another?

I have 2 tables 

EXPERTISE
EID,SCODE
101,4001
101,4002
101,4003
101,4004
101,4005
101,4006
102,4002
102,4003
102,4006
103,4001
103,4006
103,4004
104,4004
104,4006
105,4002
105,4003
105,4004
105,4006
106,4001
106,4002
106,4003
106,4004
106,4005
106,4006

and

SPECIALTY

SCODE,SNAME
4001,Cardiology
4002,Pediatrics
4003,Oncology
4004,Psychiatry
4005,Opthamology
4006,Internal

How do I formulate a query that returns the EID(s) who specialize in EVERY specialty?

Upvotes: 1

Views: 73

Answers (2)

realnumber3012
realnumber3012

Reputation: 1062

SELECT EID
FROM EXPERTISE
GROUP BY EID
HAVING COUNT(SCODE) = (SELECT COUNT(*) FROM SPECIALTY)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think the simplest method is aggregation and counting:

select e.eid
from expertise e
group by e.eid
having count(*) = (select count(*) from specialties);

This assumes that the tables do not have duplicates, which seems reasonable based on your sample data. If there are duplicates, you can just use count(distinct) instead of count(*).

Upvotes: 2

Related Questions