CODEWITHSUNDEEP
sql-server
Einar Einarsson
Einar Einarsson

Reputation: 35

SQL - Find patient that has all availabe diseases

I have the following tables

Patient

pID <<PK>>

pName

DiseaseList

dName <<PK>>

SuffersFrom (dName and pID are also PK for SuffersFrom)

dName <<FK>> 

pID <<FK>>

What I want to do is SELECT the name of the the Patient that suffers from all the diseases in DiseaseList but I really don't know how I'd do this.

I have tried searching the web but didn't find anything that helped me. This is the only task I have left to do for a SQL introduction class and I really cannot think of how to set up the query.

Upvotes: 1

Views: 781

Answers (1)

juergen d
juergen d

Reputation: 204756

Group by the patient and take only those having the same number of different diseases as there are in the disease table

select p.pid, p.pname
from patient p
join suffersFrom s on s.pid = p.pid
group by p.pid, p.pname
having count(distinct s.dname) = (select count(*) from DiseaseList)

Upvotes: 1

Related Questions