Flattening SQL Query result?

Hey all,

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

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.