Unanswered: Multiple inner joins on same field or nested query?
I'm wondering how I can structure an SQL statement to perform either a multiple join on a single table, or possibly using a sub-query. Basically, I've got one table in which both fields are foreign keys to another table, as follows:
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? Is it possible to do two inner joins on the same table? (I've tried this, unsuccessfully!) Any help is greatly appreciated!
two joins to the same table, using table aliases to distinguish which table the rows are from, and column to distinguish the columns in the result set
select lead.employee_name as teamleader
, bkup.employee_name as backup
join table1 as lead
on teamleader_employee_id = lead.employee_id
join table1 as bkup
on backup_employee_id = bkup.employee_id