>I have a parent table that needs to make two separate references back to the same table and I am having trouble forming a SQL SELECT command to do this.
>
>Example: A table that holds escrows has a Foreign Key (FK) back to the Primary Key in the contact person's table for the escrow officer. Simple enough. But I also want to have a Foreign Key reference the PK in the same contact person's table for the lender as well. The contact person's table has a type field that distinguishes the different types of people. How would I create a SQL Select statement that knows which person is the escrow officer and which one is the lender? How do I get around the "Duplicate Alias" error message. Was it bad design to put all my people: agents, lenders, escrow officers, etc... in the same table?
>
>- Paul
select es.FieldA, es.FieldB, ;
pr.FieldA as OfficerA, pr2.FieldA as LenderA, ... ;
from escrows es ;
inner join persons pr on es.officerFK = pr.personID ;
inner join persons pr2 on es.lenderID = pr.personIDCetin
