CODEWITHSUNDEEP
sqlt-sql
wootscootinboogie
wootscootinboogie

Reputation: 8705

T-SQL using where NOT Exisits

enter image description here

I have a bit attribute called 'tobacco' in tblVisits. tblICD is a list of medical codes and icd_jxn is the junction table. tblICD.Descrip is a text description of the medical code (ICD_ID). I'm trying to see if anyone who does NOT have the string 'tobacco' in their list of medical codes has their bit 'tobacco' attribute set to one. The query below is my best guess, but it's not correct. 

SELECT tblVisits.kha_id, tblVisits.tobacco from tblvisits
WHERE NOT EXISTS (SELECT icd_jxn.kha_id, icd_jxn.icd_fk, tblICD.icd_id, tblICD.Descrip
                   FROM icd_jxn inner join 
                        tblICD on icd_jxn.icd_fk = tblicd.icd_id
                       WHERE (tblicd.descrip like N'%tobacco%') AND (tblVisits.kha_id = icd_jxn.kha_id) and (tblvisits.tobacco=1))

Upvotes: 0

Views: 85

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

This query seems simpler if you think of it as summarizing a patient. Does the patient have the tobacco attribute set? Does the patent have tobacco in the description?

The following query uses this logic:

select *
from (select tv.kha_id, tv.tobacco,
             MAX(case when charindex(N'tobacco', ticd.descrip) > 1 then 1 else 0 end) as tobacco_description,
      from tblvisits tv join
           icd_jxn jxn
           on tv.kha_id = jxn.kha_id join
           tblICD ticd
           on jxn.icd_fk = ticd.icd_fx
      group by tv.kha_id, tv.tobacco
     ) t
where tobacco_description = 1 and tobacco_description = 0

The subquery summarizes the data at the patient level. You could eliminate it, by adding an appropriate "having" clause. However, I find the subquery more readable and it should have the same performance. Also, it makes it possible to count all the different combinations of tobacco, the bit, and tobacco the description.

Finally, I replaces the "like" with "charindex" because the latter is more efficient.

Upvotes: 1

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

case not guaranted

select v.kha_id, v.tobacco
from tblvisits v
where v.tobacco = 1
and not exists (select null from icd_jxn j
     inner join tblICD i on j.icd_ICD_FK= i.IDC_ID
     where j.KHA_ID = v.KHA_ID
     and i.Descrip like N'%tobacco%');

Upvotes: 1

Related Questions