Actually, given that there may not always be a backup_employee field (can be null) I think I'm forced to use an outer join to return all team leader records regardless if a matching backup_employee record exists. I'll test yours out and see.
Thank you! -----Original Message----- From: Michael Glaesemann [mailto:[EMAIL PROTECTED] Sent: January 30, 2004 11:21 PM To: Anony Mous Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Two joins on same foreign key On Jan 31, 2004, at 7:03 AM, Anony Mous wrote: > Table #1 > employee_id (pk) > employee_name > > Table #2 > teamleader_employee_id > backup_employee_id > > both fields in table 2 need to do a lookup in table 1 to get the name > of the actual employee. Do I need to use nested queries to accomplish > this? Any help is greatly appreciated! I think you could handle this by calling table1 twice, but with different aliases, like so SELECT leader.employee_name, backup.employee_name FROM table1 leader, table1 backup, table2 t2 WHERE leader.employee_id = t2.teamleader_employee_id AND backup.employee_id = t2.backup_employee_id Does that work for you? Michael Glaesemann grzm myrealbox com ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
