CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008
Mo T
Mo T

Reputation: 450

Getting help in complicated query for mysql

having this piece of schema

Doctor(licence_no, doctor_name, specialty)

Patient(pat_id, pat_name, pat_address, pat_phone, date_of_birth)

Visit(licence_no, pat_id, date, type, diagnosis, charge)

and i want to get the names of all patients who visited all doctors i did it as follows

SELECT p.pat_name FROM Patient p

INNER JOIN Visit v ON (v.pat_ID = p.pat_ID)

INNER JOIN Doctor d ON (d.licence_no = v.licence_no)

WHERE (SELECT d.licence_no FROM Doctor d) = (SELECT v.licence_no FROM Visit v2 WHERE 

p.pat_ID =v.pat_ID)

but there is an error as the sub query returns more than 1 value and i have to make sure that this patient had visited all doctors, any suggestions to make this?

Upvotes: 2

Views: 330

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270609

To be sure that a patient has visited all doctors, you can compare a distinct count of doctors (license_no) to the counts of license_no grouped by pat_id in Visit.

Something like this may do the job:

SELECT pat_name
FROM Patient p JOIN Visit v ON p.pat_id = v.pat_id
GROUP BY v.pat_id 
HAVING COUNT(DISTINCT v.license_no) = (SELECT COUNT(license_no) FROM Doctor);

The idea here is merely to verify that if there are, for example 5 doctors in Doctor, the patient has visited 5 distinct doctors in Visit.

This is, of course, untested.

Upvotes: 3

Related Questions