two subqueiries in main query

[MichaelaLee]

Hi All,
I have the following tables:
Patients:
PatientId
Name
DoctorId
TheripistId
ect...
DoctorLookUp
KeyId
DoctorName
The doctor lookup table contains all doctors and Theripist in one table. Now I need to run a query that will display the name of the Doctor and Theripist when I query the Patients table. I've tried several things and can't seem to get it right. Could you please give me some examples that could work. Thanks for any help.
Michael Lee

 

[checkai]

select 	p.PatientID,
	p.name as Patient,
	ISNULL(d.doctorName,'No Doctor') as Doctor,
	ISNULL(t.doctorName,'No Therapist') as Therapist
from	Patients p
	left join doctorLookUp D on p.doctorID = d.keyID
	left join doctorLookUp T on p.therapistID = t.keyID

 

[MichaelaLee]

Hi checkai,
Thank you for the reply. I tried your sample and it worked great with a few minor name changes, but I now need to add one more table to the query. I have the following table
Admission:
PatientId /* Link to Patients table */
Fee
AddThisField
Ect...
Say I need to add AddThisField to the query. Whats the best way to do that.
Here is what I had so far:
select p.PatientID,p.Birthdate,
p.Last_Name + ', ' + p.First_Name as Patient,
ISNULL(d.DocsName ,'No Doctor') as DoctorName,
ISNULL(t.DocsName,'No Theripist') as TherapistName,
a.Admission_Date, a.Medicaid_Number
from Patients p, Admissions a
left join DoctorFullNames D on p.doctor = d.keyID
left join DoctorFullNames T on p.therapist = t.keyID
LEFT JOIN Admissions ON p.PatientID = a.PatientId

Thanks for any suggestions. And thanks again for your reply.
Michael Lee

 

[checkai]

select p.PatientID,p.Birthdate, 
    p.Last_Name + ', ' + p.First_Name as Patient,
    ISNULL(d.DocsName ,'No Doctor') as DoctorName,
    ISNULL(t.DocsName,'No Theripist') as TherapistName,
a.Admission_Date, a.Medicaid_Number,a.Addthisfield
from    Patients p
    left join DoctorFullNames D on p.doctor = d.keyID
    left join DoctorFullNames T on p.therapist = t.keyID
    LEFT JOIN Admissions a ON a.PatientID = p.PatientId