CODEWITHSUNDEEP
sqlsql-servert-sql
ZeRaTuL_jF
ZeRaTuL_jF

Reputation: 592

Not exists is filtering out too many results?

I'm having a problem trying to find out why this query is not returning any records.

I have a query that is as follows

     SELECT c.desc
     FROM tableA a
     INNER JOIN TableB b ON a.name = b.name
     INNER JOIN TableC c ON b.id= c.Id
     WHERE NOT EXISTS (SELECT 1
                       FROM TableC 
                       WHERE tablec.desc IN 
                        ( SELECT DISTINCT d.desc
                          FROM TableD d 
                          INNER JOIN TableE on e.Id = d.Id 
                        )
                      )
        AND c.active =1
        AND b.active=1

Now the inner query of the not exists by itself returns 62 records, the outer query without the not exists returns 232, but when ran as above they return 0 and I'm not sure why. the 62 records are unique and the 232 are also unique now there is an overlap of 31 records so I would expect the posted query to return 201 records but I'm getting zero. Sorry for formatting but I'm posting this from my phone.

Upvotes: 0

Views: 65

Answers (1)

Hart CO
Hart CO

Reputation: 34774

You have not correlated the query in the NOT EXISTS to the outer query, this should be done with a WHERE clause, a simple example:

SELECT *
FROM Table1 a
WHERE NOT EXISTS (SELECT 1
                  FROM Table2 b
                  WHERE a.ID = b.ID
                  )

Though, I'd expect that to not filter any rows, so there must be another issue.

Here's a stab at fixing your query, I noticed a missing alias for the TableE:

SELECT C.DESC
FROM TABLEA A
INNER JOIN TABLEB B     ON A.NAME = B.NAME
INNER JOIN TABLEC C     ON B.ID= C.ID
WHERE NOT EXISTS (SELECT 1
                  FROM TABLEC C2
                  WHERE C2.DESC IN (SELECT DISTINCT D.DESC
                                    FROM TABLED D 
                                    INNER JOIN TABLEE E ON E.ID = D.ID )
                        AND C2.DESC = C.Desc  --Relationship between inner/outer
                  )
  AND C.ACTIVE = 1
  AND B.ACTIVE = 1

Ultimately the whole thing can likely be cleaned up depending on the relationship between TableC and TableD, maybe simply:

SELECT C.DESC
FROM TABLEA A
INNER JOIN TABLEB B     ON A.NAME = B.NAME
INNER JOIN TABLEC C     ON B.ID= C.ID
WHERE C.DESC NOT IN (SELECT DISTINCT D.DESC
                     FROM TABLED D 
                       INNER JOIN TABLEE E ON E.ID = D.ID )                     
                    )
  AND C.ACTIVE = 1
  AND B.ACTIVE = 1

Upvotes: 2

Related Questions