Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    35

    Unanswered: Multiple inner joins on same field or nested query?

    Hi,

    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:

    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? Is it possible to do two inner joins on the same table? (I've tried this, unsuccessfully!) Any help is greatly appreciated!

    -PJ

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    Code:
    select lead.employee_name as teamleader
         , bkup.employee_name as backup
      from table2
    inner
      join table1 as lead
        on teamleader_employee_id = lead.employee_id      
    inner
      join table1 as bkup
        on backup_employee_id = bkup.employee_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Posts
    35

    Smile Thanks!

    Thank you very much. Works like a charm!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •