I've searched for this and can't seem to find an answer. I apologize in advance as I'm sure this answer is out there, but I can't seem to find it.
I'm working with a SQL Server 2005 DB, and I'm aware that the query below doesn't represent a normalized DB, since the numPlacements field is in both the detail and rollup table. I didn't create the DB.
The below SQL gives the expected result when the where clause is used. The expected result is all rows where a matching value is missing from either table, or the two values don't match.
However, if I comment the where clause and uncomment the final AND in the ON clause, it returns over 200k rows instead of the expected 120 results.
SELECT CASE WHEN A.ID is NULL THEN B.ID ELSE A.ID END,
A.numPlacements AS 'AnumPlacements',
B.numPlacements AS 'bnumPlacements',
B.numPlacements - A.numPlacements as 'Variance'
FROM (SELECT ID,
Sum(numPlacements) AS 'numPlacements'
FROM PlacementDetailLevel
GROUP BY ID) A
FULL OUTER JOIN (SELECT ID,
Sum(numPlacements) AS 'numPlacements'
FROM PlacementRollupLevel
GROUP BY ID) B
ON A.ID = B.ID
--AND B.numPlacements <> A.numPlacements
WHERE A.numPlacements <> B.numPlacements or A.numPlacements is null or B.numPlacements is null
Any ideas as to why?
Aucun commentaire:
Enregistrer un commentaire