Multiple joins in two tables

Gurr · #1 (**permalink**) 05-12-06, 05:45

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!

r937 · #2 (**permalink**) 05-12-06, 07:11

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

Gurr · #3 (**permalink**) 05-12-06, 09:13

Thanks,

Works super!
Made my friday.

Internet Services

Web Development

Digital Marketing

Consumer Tech