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

Tim Biegeleisen · Accepted Answer · 2018-03-19 05:53:59Z

3

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

answered Mar 19, 2018 at 5:53

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jwolf · Accepted Answer · 2018-03-19 05:55:51Z

2

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.

answered Mar 19, 2018 at 5:55

jwolf

9388 silver badges13 bronze badges

2 Comments

Miracle Over a year ago

its working too. but whats the difference of your answer with @Tim Biegeleisen?

jwolf Over a year ago

@SurfaceTension - Not much except Tim uses left joins - the rest is just different names for the aliases. Having left joins means that the references don't necessarily have to exist in the tbl_emp table and if they don't exist, you'll still get results for every row in tbl_from_to just some of them might contain 'nulls'. Mine says they do need to to exist and if they don't, the row with the bad reference is essentially considered invalid and is excluded. If the references always exist, then the results are the same either way.

Collectives™ on Stack Overflow

Oracle SQL Join

2 Answers 2

Demo

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related