How to treat a row as a table

oracle

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

Best Answer

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;
Related Question