homebrew01 (10/6/2013)

---

ChrisM@Work (10/1/2013)

---

This I reckon is the correct way to write your query:

select T2.COL1, T2.COL2, T1.COL1, T1.COL2, T1.COL3

from T1

join T2

on T1.COL4 = T2.COL3

where T2.COL4 != 'data1'

and T1.COL1 is not NULL

and T1.COL5 is not NULL

and T1.COL6 = 'data2'

AND NOT EXISTS (SELECT 1 FROM T3

WHERE T3.COL1 = T2.COL1

AND T3.COL2 = T2.COL2

AND T3.COL3 = T1.COL1

AND T3.COL4 = T1.COL2)

Check the results against the SELECT part of your original.

So in this case, it will select from T1 and T2, only if not in T3 ?

What if you want to include non-matches against T3 ? Would you add to the WHERE clause ?

and T3.COL1 is NULL

and T3.COL2 is NULL

and T3.COL3 is NULL

and T3.COL4 is NULL