How do I use an Outer Join between tables to determine non-matching records

AndyH79
AndyH79 used Ask the Experts™
on
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.  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You were very close ...

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] AND B.meterKey = U.MeterKey
WHERE U.DateTime IS NULL
ORDER BY B.MeterKey, B.DateTime DESC

Open in new window

Commented:
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial