Script objective: get a list of numbers from a list where the numbers don't show up in another list.
The complications: the other list of numbers can only be obtained via a complicated script - and for some reason I'm getting no results when I KNOW there should be; since the first list will contain ALL the numbers and the second list of numbers will only contain some numbers; so I should be getting some results.
The script I have written (censored)
SELECT A.Number
FROM sometable AS A
INNER JOIN othertable AS B
ON A.Data = B.Data
INNER JOIN othertable2 AS C
ON B.Data = C.Data
INNER JOIN othertable3 AS D
ON C.Data = D.Data
WHERE D.Data = 'int'
AND NOT EXISTS (SELECT DISTINCT A.Number
FROM sometable AS C
anothertable AS B
ON C.Data = B.Data
INNER JOIN anothertable AS E
ON B.Data = E.Data
INNER JOIN anothertable AS A
ON E.Data = A.Data
CROSS apply (SELECT DG.Data
FROM atable AS DG
WHERE B.Data = DG.Data) D
WHERE D.Data IN ( 'int', 'int', 'int', 'int' ))
If I run the part1 (before the non exist) it works fine
If I run part2 (the data within the non exist) it also works fine - with different, and less results (containing numbers from part1)
But together they don't. So I need to know how to do this if not exist is not what I need to use?
Your Query is working as it should. Since there are some rows exists in your sub query the outer query didn't produce any result but that is not the right way to complete your objective
Your Objective
Get a list of numbers from a list where the numbers don't show up in another list.
can be done in two ways.
Using Not Exists
SELECT A.Number
FROM sometable AS A
INNER JOIN othertable AS B
ON A.Data = B.Data
INNER JOIN othertable2 AS C
ON B.Data = C.Data
INNER JOIN othertable3 AS D
ON C.Data = D.Data
WHERE D.Data = 'int'
AND NOT EXISTS (SELECT 1
FROM sometable AS CC
INNER JOIN anothertable AS BB
ON Cc.Data = BB.Data
INNER JOIN anothertable AS EE
ON BB.Data = EE.Data
INNER JOIN anothertable AS AA
ON EE.Data = AA.Data
CROSS apply (SELECT DG.Data
FROM atable AS DG
WHERE BB.Data = DG.Data) DD
WHERE DD.Data IN ( 'int', 'int', 'int', 'int' )
AND aa.number = a.number)
Or by using Not IN
SELECT A.Number
FROM sometable AS A
INNER JOIN othertable AS B
ON A.Data = B.Data
INNER JOIN othertable2 AS C
ON B.Data = C.Data
INNER JOIN othertable3 AS D
ON C.Data = D.Data
WHERE D.Data = 'int'
AND A.Number NOT IN (SELECT AA.number
FROM sometable AS CC
INNER JOIN anothertable AS BB
ON Cc.Data = BB.Data
INNER JOIN anothertable AS EE
ON BB.Data = EE.Data
INNER JOIN anothertable AS AA
ON EE.Data = AA.Data
CROSS apply (SELECT DG.Data
FROM atable AS DG
WHERE BB.Data = DG.Data) DD
WHERE DD.Data IN ( 'int', 'int', 'int', 'int' )
AND aa.number = a.number)