sql query help - how to properly join?

Question

Let's say if I have a table "PersonData" in which 4 of its columns are

FromPersonId, FromEmployeeId, ToPersonId, ToEmployeeId

Any of the records only ever contains Only one From** and Only one To** and the other two columns are null. Also FromPersonId and ToPersonId belong to "Person" table and FromEmployeeId and ToEmployeeId belong to "Employee" table.

My question is: How do I PROPERLY join PersonData with Person and Employee table?

Please note I have tried bunch of different approaches but when I do a count on the PersonData I am getting different results when doing a join (i.e. it is showing more than what is in PersonData)...

How should I be doing a join? should I do one for each FromPersonId and ToPersonId to Person and similar for the Employee? (doubt this to be the case)...

Thanks,

Voodoo

Answer 1

Ok, so it sounds like the problem is that if you join to Person on FromPersonID=PersonID and ToPersonId=PersonID, you get 2 records for each record in PersonData.

The way around this would be to join to 2 aliased copies of PersonData so you can attach the results into the same row. Something like:

Select * from PersonData
LEFT JOIN Person p1 on p1.PersonID = FromPersonID
LEFT JOIN Person p2 on p2.PersonID = ToPersonID

and basically do the same for the to and from employee fields