Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    15

    Unanswered: Multiple joins in two tables

    Hi,

    Here is the problem:
    Got table empl:
    Code:
    empl_nr       empl_name    empl_adress
    001                 JOe                   -
    002                 Sara                  - 
    003                 Mark                  -
    and table ledg:

    Code:
    account_nr    approver_nr        requester_nr
    1234                 002                    001
    3455                 003                    002
    5678                 002                    003
    Basicly it is for internal sales. Thus in Ledg, approver_nr and requester_nr correspond
    with empl_nr in the empl table.

    I want to display the account_nr, approver name and requester name in a query.
    Thus how I see it I have two references in empl for each one reference in Ledg.

    I figured I can create a temp table with fields : account_nr, approver_name and
    requester name. Then do a select into statement first to get approver name and then another statement to get requester name. And then display temp table.
    Efficient?


    Can anyone help me out on this?

    Thanks in advance!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    temp table efficient? no
    Code:
    select L.account_nr
         , A.empl_name as approver
         , R.empl_name as requester
      from ledg as L
    inner
      join empl as A
        on A.empl_nr = L.approver_nr
    inner
      join empl as R
        on R.empl_nr = L.requester_nr
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    15
    Thanks,

    Works super!
    Made my friday.

Posting Permissions

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