Need to Patients who Exist in a Table with One Value, but Not Another Value

[kjohns]

I have a table, tblPatientDiagnosis that is a many-to-many table. It hold PatientIntakeFormIDs (unique identifier for patient) and DiagnosisIDs. Obviously, each patient can have multiple entries.

I am trying to pull patients who have X diagnosis, that do NOT have an entry for a Y type of diagnosis. (I would still like to pull the patient if she has X diagnosis and has A, B, and C diagnoses, as long as she doesn't have Y diagnosis.) In order to do this I've created aliases for the tblPatientDiagnosis table.

Here's what I have but I am finding that it's pulling patients who have Y diagnosis even though I've asked it not to. I'm guessing this will require some sort of Outer Join, but I am not finding the right combination.

I'd really appreciate any help!!
Thanks.
Kate

SELECT DISTINCT TTTS.IntakeFormID AS IntakeFormID, dbo.tblPatientInfo.LastName AS LName, dbo.tblPatientInfo.FirstName AS FName
FROM dbo.tblPatientDiagnosis PI LEFT OUTER JOIN
dbo.tblPatientDiagnosis TTTS ON PI.IntakeFormID = TTTS.IntakeFormID INNER JOIN
dbo.tblPatientIntakeForm ON PI.IntakeFormID = dbo.tblPatientIntakeForm.IntakeFormID
AND PI.IntakeFormID = dbo.tblPatientIntakeForm.IntakeFormID INNER JOIN
dbo.tblPatientInfo ON dbo.tblPatientIntakeForm.fkPatientID = dbo.tblPatientInfo.PatientID
WHERE (PI.DiagnosisID = 305)
AND (TTTS.DiagnosisID NOT IN (346, 347, 348, 349, 350, 351, 352, 353))

 

[SQL_Hell]

Hi there

Is this a SQL SERVER question? it looks suspiciously like an MS Access question

If so what version of sql server?

 

[kjohns]

Re: Need to Find Patients who Exist in a Table with One Value, but Not Another Value

I'm using SQL Server Enterprise Manager version 8.0.