CODEWITHSUNDEEP
sqlsql-server-2008
wootscootinboogie
wootscootinboogie

Reputation: 8695

WHERE NOT EXISTS not working SQL Server 2008

enter image description here

This is a related question to this but with EXISTS instead of IN

SELECT distinct  Patient.patientid
FROM Patient P
JOIN patientICD pICD 
  ON P.patientid = pICD.patientid
  AND P.admissiondate = pICD.admissiondate
  AND P.dischargedate = pICD.dischargedate
JOIN tblICD 
  ON pICD.primarycode = tblICD.ICD_ID
WHERE  tblICD.descrip LIKE N'%diabetes%'
  AND patient.patientID not in (
         SELECT distinct Patient.patientid
         FROM Patient P
         JOIN patientICD pICD 
           ON P.patientid = pICD.patientid
           AND P.admissiondate = pICD.admissiondate
           AND P.dischargedate = pICD.dischargedate
         JOIN tblICD ON pICD.primarycode = tblICD.ICD_ID
         WHERE tblICD.icd_id =N'25000'
  )

This query works fine, but when I try to add another field to the select expressions I have to use WHERE EXISTS and the following query returns 0 results, while the above query returns 130. I'm trying to add the tblicd.icd_id field to the result set from the first query and I'm doing something wrong.

SELECT DISTINCT  Patient.patientid, tblicd.icd_id
FROM Patient
INNER JOIN patientICD 
  ON Patient.patientid = patientICD.patientid 
  AND Patient.admissiondate = patientICD.admissiondate 
  AND Patient.dischargedate = patientICD.dischargedate 
INNER JOIN tblICD 
  ON patientICD.primarycode = tblICD.ICD_ID
WHERE tblICD.descrip LIKE N'%TextOfNote%'
  and not exists (
      SELECT DISTINCT Patient.patientid, tblicd.icd_id
      FROM Patient
      INNER JOIN patientICD
        ON Patient.patientid = patientICD.patientid 
        AND Patient.admissiondate = patientICD.admissiondate 
        AND Patient.dischargedate = patientICD.dischargedate 
      INNER JOIN tblICD 
        ON patientICD.primarycode = tblICD.ICD_ID
      WHERE tblICD.icd_id = N'25000'
)

Upvotes: 0

Views: 4244

Answers (2)

Kevin Aenmey
Kevin Aenmey

Reputation: 13419

I haven't run this against your schema, but give this a try

EDIT...

SELECT DISTINCT  Patient.patientid, tblicd.icd_id
FROM Patient 
    INNER JOIN patientICD 
        ON Patient.patientid = patientICD.patientid 
        AND Patient.admissiondate = patientICD.admissiondate 
        AND Patient.dischargedate = patientICD.dischargedate 
    INNER JOIN tblICD 
        ON patientICD.primarycode = tblICD.ICD_ID
WHERE tblICD.descrip LIKE N'%TextOfNote%'
    AND tblICD.icd_id <> N'25000'

Upvotes: 1

sam yi
sam yi

Reputation: 4934

You have two patient, patientICD, tblICD at play here.. one in the main query and another in the "not exists." Try using table alias.

SELECT X FROM TABLEA A
INNER JOIN TABLEB B
  ON A.ID = B.ID

You have to reference the main query inside the "not exists"... pay close attention to the "ON" join condition and the where clause.

WHERE     (tblICD.descrip LIKE N'%TextOfNote%') and not exists
(
SELECT    DISTINCT Patient.patientid, tblicd.icd_id
FROM         Patient INNER JOIN
 patientICD ON Patient.patientid = patientICD.patientid AND Patient.admissiondate = patientICD.admissiondate AND 
                      Patient.dischargedate = patientICD.dischargedate INNER JOIN
                      tblICD ON patientICD.primarycode = tblICD.ICD_ID
WHERE     (tblICD.icd_id = N'25000')
)

Upvotes: 0

Related Questions