CODEWITHSUNDEEP
sql-servert-sql
Naigel
Naigel

Reputation: 9644

SQL not exists returning query values

I'm having some trouble with a query to check differences between 2 identical tables with different rows. This is the query

SELECT *
FROM [PROD01].[myDefDB].[forward].[fv] as DB01
WHERE TargetDate = '20150429' and
NOT EXISTS (SELECT *
    FROM [PROD02].[myDefDB].[forward].[fv] as DB02
    WHERE DB02.TargetDate = '20150429' and
        DB02.Id_Fw = DB01.Id_Fw and
        DB02.Id_Bl = DB01.Id_Bl and
        DB02.Id_Pt = DB01.Id_Pt and
        DB02.TargetDate = DB01.TargetDate and
        DB02.StartDate = DB01.EndDate and
        DB02.EndDate = DB01.EndDate and
        DB02.[Version] = DB01.[Version]
)

Consider that [PROD02].[myDefDB].[forward].[fv] is a subset of [PROD01].[myDefDB].[forward].[fv], that performing a SELECT count(*) on both tables for the TargetDate = '20150429' returns me 2367 and 4103, so I expect to get 1736 from that query but I get more than 2000.

I considered all PKs in the WHERE clause. What am I missing?

Upvotes: 0

Views: 75

Answers (1)

ughai
ughai

Reputation: 9890

You can use EXCEPT like this.

SELECT Id_Fw,Id_Bland,Id_Pt,TargetDate,StartDate,EndDate,[Version]
FROM [PROD01].[myDefDB].[forward].[fv] as DB01
WHERE TargetDate = '20150429'
EXCEPT
SELECT Id_Fw,Id_Bl,Id_Pt,TargetDate,StartDate,EndDate,[Version]
FROM [PROD02].[myDefDB].[forward].[fv] as DB02
WHERE TargetDate = '20150429'

This will get you all the rows in PROD01 which are not in PROD02

Upvotes: 3

Related Questions