Re: Two joins on same foreign key

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:grzm@myrealbox.com]
Sent: January 30, 2004 11:21 PM
To: Anony Mous
Cc: pgsql-general@postgresql.org
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