Avatar of Jim Metcalf
Jim Metcalf
Flag for United States of America

asked on 

help with join on 3 tables and identify records that did end up in results

I have two systems.  

I will name them

1) OldSystem
2) NewSystem

There are 3 tables involved with this query.

1)OldSystemRoute  -
           Needed information from this table (Accountid) column
           Connects to NewSystemRoute by the (Meterid) column
2)NewSystemRoute -        (This table is used to connect tables OldSystemRoute & NewSystemRead
           Needed Information from this table (nothing)
           connects to OldSystemRoute by the (meterid) column
           connects to the NewSystemRead table by the (AccountID) column
          Needed Information from this table (MeterRead) column
My desired result is the AccountID  from the oldsystem to connect to the meterread from the new system.  The problem is they don't share unique account id's or anything else you can directly connect them to between the two tables.  The third table that is needed is NewSystemRoute.  This has connectors to the other two tables.

I am able to run my query below and get MOST of the desired information

Select  oldsystemroute.accountid, Newsystemread.MeterRead
from newsystemread
inner join NewSystemRoute
on Newsystemread.accountid = Newsystemroute.accountid
inner join OldSystemRoute
on oldsystemroute.meterid = NewSystemRoute.meterid

Here is my problem because I am dealing with thousands of reads.
How do I identify Reads that took place on the NewsystemReadsTable but did not end up in my results from the above query

I need to be able to provide the old system some readings we take out in the field.  I have accomplished this
Microsoft SQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon