SQL Query--is not excluding a segment of my data

CMCITD
CMCITD used Ask the Experts™
on
I have the following code;

select distinct pt.person_id, p.last_name, p.first_name, p.address_line_1, p.city, p.state, p.zip from 
patient_ pt
INNER JOIN vital_signs_ vs ON vs.person_id = pt.person_id
INNER JOIN patient_problems pp ON pp.person_id = pt.person_id
INNER JOIN lab_results_obx lr ON lr.person_id = pt.person_id
INNER JOIN person p on p.person_id = pt.person_id
where pt.age >= 18 AND vs.BMI_calc >= 22 
and ((lr.obs_id like '25000000^GLUCOSE^' and CAST(lr.observ_value  AS decimal)>100) OR (lr.obs_id like '%FM HEM A1C^HEMOGLOBIN A1C^I%')  
and NOT EXISTS (select NULL from patient_problems p1 where (  description  like 'Diabetes%')
AND pt.person_id = p1.person_id))
order by p.last_name desc

Open in new window


When I check my data--everythings appears correct, except for this;

and NOT EXISTS (select NULL from patient_problems p1 where (  description  like 'Diabetes%')
AND pt.person_id = p1.person_id))

Open in new window


I have patients showing up that have Diabetes listed in their patient problem table.  The exact name of the patient_problem is 'Diabetes Type II' so the like statement should be catching it.

  I had posted this previously and after checking data, thought it was clean--however after I went 20+ names in diabetic patients started showing up.  Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
I believe that's due to NULL comparison which doesn't work like that - please try updated code below:

select distinct pt.person_id, p.last_name, p.first_name, p.address_line_1, p.city, p.state, p.zip
from patient_ pt
INNER JOIN vital_signs_ vs ON vs.person_id = pt.person_id
INNER JOIN patient_problems pp ON pp.person_id = pt.person_id
INNER JOIN lab_results_obx lr ON lr.person_id = pt.person_id
INNER JOIN person p on p.person_id = pt.person_id
where pt.age >= 18 AND vs.BMI_calc >= 22
      and ((lr.obs_id like '25000000^GLUCOSE^' and CAST(lr.observ_value  AS decimal)>100) OR (lr.obs_id like '%FM HEM A1C^HEMOGLOBIN A1C^I%')  
      and NOT EXISTS (select person_id from patient_problems p1 where (  description  like 'Diabetes%')
      AND pt.person_id = p1.person_id))
order by p.last_name desc

Author

Commented:
Ok--tried that code---same patients are still appearing
Database Analyst
Commented:
This may make it slower as full pattern search will invalidate any existing index on description column but can you please try again with code below

select distinct pt.person_id, p.last_name, p.first_name, p.address_line_1, p.city, p.state, p.zip
from patient_ pt
INNER JOIN vital_signs_ vs ON vs.person_id = pt.person_id
INNER JOIN patient_problems pp ON pp.person_id = pt.person_id
INNER JOIN lab_results_obx lr ON lr.person_id = pt.person_id
INNER JOIN person p on p.person_id = pt.person_id
where pt.age >= 18 AND vs.BMI_calc >= 22
      and ((lr.obs_id like '25000000^GLUCOSE^' and CAST(lr.observ_value  AS decimal)>100) OR (lr.obs_id like '%FM HEM A1C^HEMOGLOBIN A1C^I%'))  
      and NOT pt.person_id IN (select person_id from patient_problems p1 where ([description] like 'Diabetes%') AND pt.person_id = p1.person_id)
order by p.last_name desc

Author

Commented:
Sorry was in a clinic in the last couple of days, thanks so much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial