AndyH79
asked on
How do I use an Outer Join between tables to determine non-matching records
I have a table which stores my usage values(20mil records) where each record is identified by it's MeterKey(int) and DateTime(smalldatetime) columns. The second table is a batch table which I'm using to import new data and determine if the new data has new records which need to be inserted or duplicate records which need to update the values in the usage table. I'm using the following query to determine which records require an update of existing records:
SELECT B.Action, B.MeterKey, B.DateTime, B.ReportedValue, B.ValidatedValue, B.Imported, B.Validated, B.ExportedToDW
FROM Usage_Electric AS U
JOIN Usage_Electric_BATCH AS B
ON B.DateTime=U.DateTime
WHERE B.MeterKey = U.MeterKey
Next I'm trying to determine which records do not exist in the Usage table which need to be inserted from the Batch table. I've tried to filter out duplicates by:
SELECT B.Action, B.MeterKey, B.DateTime, B.ReportedValue, B.ValidatedValue, B.Imported, B.Validated, B.ExportedToDW
FROM Usage_Electric_BATCH AS B
LEFT JOIN Usage_Electric AS U
ON B.DateTime=U.DateTime
WHERE U.DateTime IS NULL
ORDER BY B.MeterKey, B.DateTime DESC
This query only works if there are not multiple records with the same DateTime value and different MeterKey values in the Usage table. I've tried to change the WHERE clause to "WHERE U.DateTime IS NULL AND B.MeterKey = U.MeterKey" but this is not identifying the records correctly.
I've also tried to add a 2nd LEFT OUTER JOIN which didn't work:
SELECT B.Action, B.MeterKey, B.DateTime, B.ReportedValue, B.ValidatedValue, B.Imported, B.Validated, B.ExportedToDW
FROM Usage_Electric_BATCH AS B
LEFT JOIN Usage_Electric AS U
ON B.DateTime=U.DateTime
LEFT JOIN Usage_Electric AS D
ON B.MeterKey=D.MeterKey
WHERE U.DateTime IS NULL AND D.DateTime IS NULL
ORDER BY B.MeterKey, B.DateTime DESC
Any help you can give me is appreciated.
SELECT B.Action, B.MeterKey, B.DateTime, B.ReportedValue, B.ValidatedValue, B.Imported, B.Validated, B.ExportedToDW
FROM Usage_Electric AS U
JOIN Usage_Electric_BATCH AS B
ON B.DateTime=U.DateTime
WHERE B.MeterKey = U.MeterKey
Next I'm trying to determine which records do not exist in the Usage table which need to be inserted from the Batch table. I've tried to filter out duplicates by:
SELECT B.Action, B.MeterKey, B.DateTime, B.ReportedValue, B.ValidatedValue, B.Imported, B.Validated, B.ExportedToDW
FROM Usage_Electric_BATCH AS B
LEFT JOIN Usage_Electric AS U
ON B.DateTime=U.DateTime
WHERE U.DateTime IS NULL
ORDER BY B.MeterKey, B.DateTime DESC
This query only works if there are not multiple records with the same DateTime value and different MeterKey values in the Usage table. I've tried to change the WHERE clause to "WHERE U.DateTime IS NULL AND B.MeterKey = U.MeterKey" but this is not identifying the records correctly.
I've also tried to add a 2nd LEFT OUTER JOIN which didn't work:
SELECT B.Action, B.MeterKey, B.DateTime, B.ReportedValue, B.ValidatedValue, B.Imported, B.Validated, B.ExportedToDW
FROM Usage_Electric_BATCH AS B
LEFT JOIN Usage_Electric AS U
ON B.DateTime=U.DateTime
LEFT JOIN Usage_Electric AS D
ON B.MeterKey=D.MeterKey
WHERE U.DateTime IS NULL AND D.DateTime IS NULL
ORDER BY B.MeterKey, B.DateTime DESC
Any help you can give me is appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help! I didn't know you could have multiple conditions within the ON stmt. That makes alot of sense.
SELECT B.Action, B.MeterKey, B.DateTime, B.ReportedValue, B.ValidatedValue, B.Imported, B.Validated, B.ExportedToDW
FROM Usage_Electric_BATCH AS B
WHERE NOT EXISTS (SELECT *
FROM Usage_Electric
WHERE DateTime=B.DateTime )
ORDER BY B.MeterKey, B.DateTime DESC