help with join on 3 tables and identify records that did end up in results
I have two systems.
I will name them
There are 3 tables involved with this query.
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
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 query-design.pdf