Link to home
Start Free TrialLog in
Avatar of AndyH79
AndyH79Flag for United States of America

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.  
ASKER CERTIFIED SOLUTION
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Try this:
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
Avatar of AndyH79

ASKER

Thanks for the help! I didn't know you could have multiple conditions within the ON stmt.  That makes alot of sense.