SQL Server 2005 - WHERE NOT EXISTS and a complicated script inside of the NOT EXISTS

Question

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 not exist is used with a subquery, your subquery uses the same table and aliases as your outer query. This is going to keep it from being a corelated subquery and probably where your problem is coming from. Also, having the same item in an IN list, does not do anything useful. — jmoreno, Dec 28 '14 at 05:10
FYI: It useless to use `DISTINCT` inside an `EXIST` query. It does not matter **what** the query returns, just the number of rows: zero or more than zero. — PM 77-1, Dec 28 '14 at 05:12
Looks like there is a typo in Subquery `..sometable AS C anothertable AS B..` should be `..sometable AS C Inner Join anothertable AS B..` **Inner Join** is missing — Pரதீப், Dec 28 '14 at 05:23
I was using not exists in the wrong way which was the entire issue. I think I get it now. PM 77-1's response makes sense to me. @NoDisplayName - the tables were not the same in both queries; but the data would be comparable. I was trying to simplify from the real version. I did miss an inner join declaration inside of the not exists on the demo script; but not on the real one. — Lockindal Linantal, Dec 28 '14 at 18:26

score 0 · Answer 1 · answered Dec 28 '14 at 05:12

You said that the subquery, run independently, produces results. Therefore, when run in the NOT EXISTS clause, it will always produce results and therefore the clause will always be false.

My guess is that what you meant is more along the lines of WHERE A.Number NOT IN ( ... )

Pரதீப் · Accepted Answer · 2014-12-28T05:52:48.203

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)

Thanks! I think I understand why this would work and mine would not. I'll give it a try. — Lockindal Linantal, Dec 28 '14 at 18:22

SQL Server 2005 - WHERE NOT EXISTS and a complicated script inside of the NOT EXISTS

2 Answers2