0

I have two tables with the following structures:

CREATE TABLE USERS
(
  USERID VARCHAR2,
  USERNAME VARCHAR2
);

CREATE TABLE CONFIRMATIONS
(
 PERSONELID NUMBER,
 ACCOUNTING VARCHAR2,
 FINANCE VARCHAR2,
 HR VARCHAR2
);

ACCOUNTING,FINANCE,HR columns of the the CONFIRMATIONS table correspond to the USERID column in the USERS table. I need a query that will put USERNAME corresponding the USERID if in any of the ACCOUNTING,FINANCE,HR columns have a value in it. So suppose we have the following data in the tables:

USERID|USERNAME           PERSONELID | ACCOUNTING| FINANCE |  HR
---------------           ----------------------------------------
   1    Jack                  113          2          1      NULL
   2    Rony                  120          3         NULL      2
   3    Michel

I need something looking like this:

PERSONELID | ACCOUNTING | FINANCE |  HR
----------------------------------------
    113         Rony        Jack    NULL
    120        Michel       NULL    Rony
2

You need to use multiple self joins to get that information:

select c.personelid, 
       a.username as accounting, 
       f.username as finance,
       h.username as hr
from confirmations c 
  left join users a on c.accounting = a.userid
  left join users f on c.finance = f.userid
  left join users h on c.hr = f.userid;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.