Oracle SQL Join

Question

I have 2 tables as follows:

tbl_emp

emp_code    name
1           A
2           B
3           C
4           D

tbl_from_to

col_from    col_to
4           2
1           2
2           3
3           4

what I wanted is an output like this:

res_from    res_to
D           B
A           B
B           C
C           D

I tried:

select emp.name, emp.name
from tbl_emp emp
join tbl_from_to
on emp.emp_code = ft.col_from
or --also tried and
emp.emp_code = ft.col_to

and the result is like this

 res_from    res_to
 D           D
 A           A
 B           B
 C           C

Answer 1

This ought to do the trick:

select e1.name as res_from, e2.name as res_to
from tbl_from_to ft
join tbl_emp e1 on e1.emp_code = ft.col_from
join tbl_emp e2 on e1.emp_code = ft.col_to

I hope this helps.

Answer 2

Try joining the bridge table to the employee table twice:

SELECT
    t1.name AS res_from,
    t2.name AS res_to
FROM tbl_from_to tf
LEFT JOIN tbl_emp t1
    ON tf.col_from = t1.emp_code
LEFT JOIN tbl_emp t2
    ON tf.col_to = t2.emp_code;

The demo below is given in SQL Server (because I struggle to set up Oracle demos), but it should literally run cut-and-paste into Oracle.

Demo