Joining 2 fields to parent table

1

Hi all,

I have a parent table containing employee names, EMPMAST, with a 'name' field of course, and another called 'uniquekey'. Another table has two fields, 'FromEmpMastFKey' and 'ToEmpMastFKey' which both correspond to the 'uniquekey'. How could I construct a query that will return the names for both of these foreign keys? A JOIN would connect it to 1 record in EMPMAST, but these two fields 'FromEmpMastFKey' and 'ToEmpMastFKey' pertain to different names. So that my data returns like such.

MESSAGE FromEmpMastFKey Sent By ToEmpMastFKey Received
Hello 392%*#20923 John Smith 8$8)(2834!@# Jane Doe

The 'Sent By' and 'Received' are what I'm trying to get.

Thanks so much for your help. I truly appreciate it.

2

You use an "alias" on the table name. That allows you to use the same base table as many times as you need in one query:

SELECT [Ant].<col_name1>, ..., EmpFrom.name AS FromName, EmpTo.name AS ToName, ...
FROM another_table [Ant]
LEFT OUTER JOIN do.EMPMAST EmpFrom ON EmpFrom.<key_col> = [Ant].FromEmpMastFKey
LEFT OUTER JOIN do.EMPMAST EmpTo ON EmpTo.<key_col> = [Ant].ToEmpMastFKey

Edit: Corrected EmpFrom to EmpTo on second JOIN.

3

Thank you Scott.