How to remove the records

SELECT Prsn.PersonID as HospitalID,
Prsn.Name,
Prsn.Type,
Prsn.Sex,
DateDiff(year,Prsn.BirthDate,GetDate()) as Age,
IP.AttendingMD as Attending,
IP.Location,
IP.AdmitDt as AdmDate,
DateDiff(day,IP.AdmitDt,GetDate())+1 as LOS,
IP.Service,
PTList.Status as Active
FROM Person as Prsn
INNER JOIN IPList as IP
ON Prsn.PersonID = IP.PersonID
LEFT JOIN PTList
ON
Prsn.PersonID = PTList.PersonID
AND
PtList.ProviderCode='john'
Order by Name

The above will select all the records from InPatient and will retrieve either the status code ('A') or blank ('') from PTList, depending on whether or not the john has marked the record as "his".

What I want to do is to return the same dataset, but WITHOUT any records belonging to john.

I can't use <>'John' because 14 others might have the silly thing as active at the same time. What I'd really like to do is

Select * from
(the above)
Where Status=Null

I know it can be done, but I don't know how. Any ideas?I am guessing you want one of the following.

Tim S

SELECT Prsn.PersonID as HospitalID,
Prsn.Name,
Prsn.Type,
Prsn.Sex,
DateDiff(year,Prsn.BirthDate,GetDate()) as Age,
IP.AttendingMD as Attending,
IP.Location,
IP.AdmitDt as AdmDate,
DateDiff(day,IP.AdmitDt,GetDate())+1 as LOS,
IP.Service,
PTList.Status as Active
FROM Person as Prsn
INNER JOIN IPList as IP
ON Prsn.PersonID = IP.PersonID
LEFT JOIN PTList
ON
Prsn.PersonID = PTList.PersonID
AND
PtList.ProviderCode='john'
WHERE PTList.Status IS NULL
Order by Name

Second Guess

SELECT Prsn.PersonID as HospitalID,
Prsn.Name,
Prsn.Type,
Prsn.Sex,
DateDiff(year,Prsn.BirthDate,GetDate()) as Age,
IP.AttendingMD as Attending,
IP.Location,
IP.AdmitDt as AdmDate,
DateDiff(day,IP.AdmitDt,GetDate())+1 as LOS,
IP.Service,
PTList.Status as Active
FROM Person as Prsn
INNER JOIN IPList as IP
ON Prsn.PersonID = IP.PersonID
WHERE NOT EXISTS
( SELECT * FROM PTList WHERE
Prsn.PersonID = PTList.PersonID
AND
PtList.ProviderCode='john'
)
Order by Name|||yup, it was the where not exists.

I'd already tried a variant on the other one, it returned nothing, but I probably set it up incorrectly.

Just tried the other way, also. Worked like a charm. I'm not positive, butg from somewhere I got the idea that the Exists works something like an IN statement? That would imply that the first way would be faster ... at least in machine terms.

Thank you