Basic SQL Query Help (Not Exists)

Question

I have 3 tables Doctor, Patient and Visit.

Doctor Table has DoctorID, Name and City.

Patient Table has PatientID, Name and City.

Visit Table has DoctorID, PatientID, NumVisits.

I am trying to retrieve all Doctors that have not been visited by a Patient of a Certain City (Lets say New York).

I am very new at writing queries and I can't seem to get it to work.

My CODE:

SELECT DoctorId, Doctor.Name FROM Visit JOIN Doctor using(DoctorID) JOIN Patient using(PatientID) WHERE NOT EXISTS (SELECT DoctorId, Doctor.Name FROM Visit JOIN Doctor using(DoctorID) JOIN Patient using(PatientID) WHERE Patient.City = 'New York');

Can someone please explain to me what I am doing wrong? Maybe my whole approach is not correct.

You definitely don't need 6 queries to accomplish this! It would help to take a step back and think about it from a different perspective, a nice rule of thumb is you should never perform more joins than there are tables involved in your query!!! — DanHabib
– DanHabib, Commented Sep 15, 2015 at 3:12

Viktor Bardakov · Accepted Answer · 2015-09-15 03:23:48Z

You should connect your subquery with main query. Right now in your subquery you selects all doctors from New York, and of course you have at least one. Thats why WHERE NOT EXISTS (1 or more rows) will never be true. Try something like this

SELECT DoctorId, Doctor.Name FROM Visit JOIN Doctor using(DoctorID) JOIN Patient using(PatientID) WHERE NOT EXISTS (SELECT * FROM Visit JOIN Patient using(PatientID) WHERE Patient.City = 'New York') and Visit.Doctorid=Doctor.DoctorID -- Doctor.DoctorID from main query ;

And (thanks @Brad): Since Patient isn't used in the outer query, you could remove the first JOIN against Patient and Visit. In fact, you SHOULD remove the join against Patient and Visit in the outer query, or you'll miss records for doctors that have no patients. Result will be

SELECT DoctorId, Doctor.Name FROM Doctor WHERE NOT EXISTS (SELECT * FROM Visit JOIN Patient using(PatientID) WHERE Patient.City = 'New York') and Visit.Doctorid=Doctor.DoctorID -- Doctor.DoctorID from main query ;

Since Patient isn't used in the outer query, you could remove the first JOIN against Patient and Visit. In fact, you SHOULD remove the join against Patient and Visit in the outer query, or you'll miss records for doctors that have no patients.

Sateesh Pagolu · Accepted Answer · 2015-09-15 03:29:10Z

SELECT DISTINCT D.DOCTORID FROM Visit V INNER JOIN PATIENT P ON P.PATIENTID = V.PATIENTID AND P.CITY='NEWYORK' RIGHT JOIN DOCTOR D ON D.DOCTORID = V.DOCTORID WHERE P.PATIENTID IS NULL

Fiddle here : http://sqlfiddle.com/#!3/0e194/1

Mahmoud · Accepted Answer · 2015-09-15 03:11:21Z

you can do it as follow:

SELECT DoctorId, Doctor.Name From Doctor Where DoctorId NOT IN (Select DoctorId From Visit Where PatientID IN (Select PatientID From Patient Where City = 'New York'))

This way you select the patients from the specified city in the most inner selection, then you take DoctorId where visited by those patients and finally select doctors which is not among them.

Collectives™ on Stack Overflow

Basic SQL Query Help (Not Exists)

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related