We have 2 versions of a table: V1 and V2, V1 being the source of truth. I am in the process of comparing both these versions and trying to make V2 a mirror copy of V1 (there is no possibility of deleting all the contents of V2 and copying from V1).
Now, I found that there are lots of missing rows in V2 for a specific date and I need to pull them from V1 for that date. To find out what rows are available in V1 and missing in V2, I am trying LEFT EXCLUDING JOIN
:
SELECT *
FROM V1
LEFT JOIN
(
SELECT * FROM V2
WHERE
Status IN ('A','P','S')
AND Date = '2015-06-30'
) V2 ON V1.[Level 1 Code] = V2.[L1Code] COLLATE SQL_Latin1_General_CP1_CI_AS
AND V1.[Date] = V2.Date
AND V1.[Unique Identifier] = V2.Identifier COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE
V1.[Status] IN ('A','P','S')
AND V1.[Date] = '2015-06-30'
AND V2.Id IS NULL
Using this code, no row is returned. However, If I replace the Status filter to
Status = 'S'
instead of (‘A’,’P’,’S’) then it returns a row which is missing from V2, which seems right. But I need to test it for all the three statuses together.
I wanted to know why it does not work with the current way. Any help is much appreciated.
Thanks,
Kirti
I have verified to the more granular levels to do further testing and found that Status = ‘S’ works fine instead of Status in (‘A’,’P’,’S’)
I am expecting an explanation of this, or may be the correct code if my code is incorrect.
Kirti Singh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.