Hey all,
You might want to try a FULL JOIN on the System 1 table and the RateTable and then LEFT JOIN that combination to the Employee table ON COALESCE(system1Table.EmployeeID, RateTable.EmployeeID) = EmployeeTable.EmployeeID.I have two different databases that store the same info. Sometimes the data doesn't match, and I have been asked to come up with a query to find the decrepencies.
That data is an employees payrates for StraightTime OverTime and Double time. The problem is they store the data differently.
System 1 stores it
EmployeeID - Name - StraightTime - OverTime - Doubletime
System 2 Stores it
EmployeeTable:
EmployeeID - Name
RateTable
EmployeeID - Rate - RateType
Id like to return the data in a single row:
EmployeeID - Name - System1StraightTime - System2StraightTime- System1OverTime - System2OverTime ...
I tried Left Joining the System2.RateTable 3x, but it will only return the first join's records, and then the second and third join's records appear ONLY if the the record if also wrong with the first joins rows.
Is there a way to do this in SQL, of do I need to bit the bullet, and run the query 3x, and then assemble them in code with a loop?
Thanks
Eric Wild
没有评论:
发表评论