TSQL IN and NOT IN

Question

I have the below query that returns all diagnosis that do not have a '-829' value associated to them. The query does work but I think that I've made it harder than it should be. Is there a tidier way of doing this?

select distinct
c2.diagnosisn DiagnosisName, 
a2.text CareLookup
from core_patdiagnosis c1 
LEFT OUTER JOIN core_diagnosis c2 ON c1.diagnosis = c2.id 
LEFT OUTER JOIN core_carecontext c3 ON c1.carecontex = c3.id 
LEFT OUTER JOIN applookup_instance a1 ON c1.lkp_specialty = a1.id 
LEFT OUTER JOIN core_diagnosis_taxonomyma c5 ON c2.id = c5.id 
LEFT OUTER JOIN applookup_instance a3 ON c5.lkp_taxonomyna = a3.id 
LEFT OUTER JOIN applookup_instance a2 ON c3.lkp_context = a2.id

where c2.id IN
(
select c11.id core_diagnosis_c1_id
from core_diagnosis c11 
LEFT OUTER JOIN core_diagnosis_taxonomyma c21 ON c11.id = c21.id 
LEFT OUTER JOIN applookup_instance a11 ON c21.lkp_taxonomyna = a11.id
WHERE
c11.isactive = 1
and a11.id != -829 or a11.id is null
)

AND
c2.id NOT IN
(
select c12.id ProcID
from core_procedure c12
LEFT OUTER JOIN core_procedure_taxonomyma c22 ON c12.id = c22.id 
LEFT OUTER JOIN applookup_instance a12 ON c22.lkp_taxonomyna = a12.id
WHERE
c12.isactive = 1
and a12.id = -829 or a12.id is null
)

AND
c1.sys_creation_datetime > '01 Dec 2017 00:00:00' AND a2.text like 
'%Emergency%'

I doubt this is doing what you expect. And is process before or. — paparazzo, Jan 08 '18 at 13:48

score 0 · Accepted Answer · answered Jan 08 '18 at 14:02

extra stuff not used

a2.text like '%Emergency%' breaks the outer join

select distinct c2.diagnosisn DiagnosisName, a2.text CareLookup
from core_patdiagnosis  c1 
JOIN core_diagnosis     c2 ON c2.id = c1.diagnosis  
JOIN core_carecontext   c3 ON c3.id = c1.carecontex 
JOIN applookup_instance a2 ON a2.id = c3.lkp_context     

where c2.id IN
( select c11.id 
  from core_diagnosis c11 
  LEFT OUTER JOIN core_diagnosis_taxonomyma c21 ON c11.id = c21.id 
  LEFT OUTER JOIN applookup_instance a11        ON c21.lkp_taxonomyna = a11.id
  WHERE c11.isactive = 1 and a11.id != -829 
     or a11.id is null
)

AND
c2.id NOT IN
( select c12.id 
  from core_procedure c12
  LEFT OUTER JOIN core_procedure_taxonomyma c22 ON c12.id = c22.id 
  LEFT OUTER JOIN applookup_instance a12        ON c22.lkp_taxonomyna = a12.id
  WHERE c12.isactive = 1 and a12.id = -829 
     or a12.id is null
)

AND c1.sys_creation_datetime > '01 Dec 2017 00:00:00' 

AND a2.text like '%Emergency%'

`and a11.id != -829` and `and a12.id = -829` should be moved to the `on` clause and then you can get rid of the `or a11.id is null` and `or a12.id is null` — Zohar Peled, Jan 08 '18 at 14:08

score -1 · Answer 2 · answered Jan 08 '18 at 12:07

-1

You could use a CTE (Common Table Expression - see msdn page):

WITH BufferTableCTE (id) AS (...) SELECT ... FROM ... WHERE id in (SELECT id FROM BufferTableCTE WHERE id != -829) AND NOT id in (SELECT id FROM BufferTableCTE WHERE id = -829) AND ...

Another way would be to store an intermediate result in a temporary table and then use this temporary table later on (twice, once with id != -829 and once with id = 829)

Both ways would reduce your repeated sql declarations.

answered Jan 08 '18 at 12:07

Grimm

690
8
17

Would be nice to get to know, who and why someone downvoted this. Thank you. – Grimm Jan 08 '18 at 14:23
Take a closer look at the select statements – paparazzo Jan 08 '18 at 14:29
My post was a more general solution not an explicit one. Even if two tables differ you can still go for a CTE or temporary table solution. – Grimm Jan 08 '18 at 14:32
Supposed to actually answer the stated question here – paparazzo Jan 08 '18 at 14:41
"Any answer that gets the asker going in the right direction is helpful" (see https://stackoverflow.com/help/how-to-answer). You might also want to have a look at the last chapter of that document as well. – Grimm Jan 08 '18 at 14:46
Multiple CTE where a CTE is used once is not the right direction. You asked why a down vote and I answered. – paparazzo Jan 08 '18 at 14:55
Yes. Have a nice day. – Grimm Jan 08 '18 at 15:02

TSQL IN and NOT IN

2 Answers2