Unanswered: Need a double join with a where clause
SELECT Person.PersonID as HospitalID,
IPList.AttendingMD as Attending,
PTList.Status as Active
Person INNER JOIN
Person.PersonID = IPList.PersonID)
LEFT JOIN (Select PTList.PersonID,PTList.Status from PTList
WHERE PtList.ProviderCode='John') ON PTList.PersonID=IPList.PersonID
The Select after the Left Join gives an error, but when I join PTList and IPList on PersonID, the where clause gets left out in the main set. This causes me to get only the records where 'John' is the provider code.
What I want is to get all the records of IPList inner Join with Person, and then I want the PTList.Active Status for Only those records in PTList where the ProviderCode='John'
How can I get the where clause to hit ONLY on the PTList?
It IS as bad as you think
and they ARE out to get you