SQL query based on three tables with restriction

Question

I have three tables as follows

Patient (PatientNo, FamilyName, LastName, Address, State, PostCode)
Doctor (DoctorNo, Name)
Account (AccountNo, DoctorNo, PatientID, AccountType)

How do I achieve this?
Get Name and State of Patients who live in the state WA and have been treated by Dr. John or Dr. Sara
Below is my query
SELECT 
    FamilyName || ' ' || lastname AS FullName, 
    P.Address, P.state,
    D.name 
FROM
    Patient P 
LEFT JOIN 
    account A ON A.patientno = P.patientno
LEFT J OIN
    Doctor D ON A.DoctorNo = D.DoctorNo 
WHERE
    P.state = 'WA'
    AND D.name = 'John' OR D.name = 'Sara'

Anoop Narayan · Accepted Answer

Correct the property name in Account table as PatientNo.
SELECT 
FamilyName || ' ' || lastname AS FullName, 
P.Address, P.state,
D.name 
FROM
Patient P 
JOIN 
account A ON A.patientno = P.patientno
JOIN
Doctor D ON A.DoctorNo= D.DoctorNo
WHERE
P.state = 'WA'
AND D.name = 'John' OR D.name = `Sara`

seanb · Answer

You are almost correct, but you'll need to put brackets in your WHERE clause.
Currently it's doing (P.state = 'WA' AND D.name = 'John') OR D.name = 'Sara'. Therefore finding

all patients for John in WA, and
all patients for Sara, whether or not they're in WA.

I suggest P.state = 'WA' AND (D.name = 'John' OR D.name = 'Sara') or even P.state = 'WA' AND D.name IN ('John', 'Sara').
Also note that in your Account table you have PatientId but elsewhere you use PatientNo. I presume this is a typo, but please check your tables.

SQL query based on three tables with restriction

2 Answers

Add your own answers!

Ask a Question