user3144072 user3144072 - 1 year ago 65
SQL Question

Oracle Self Join and linking with another table

I have a puzzle when self joining a table. I just can't seem to work it out.

For example I have a table Employee with all employee records. I have a temporary table with two fields, Employee ID 1 and Employee ID 2. This temporary table stores the relation between two employees. (It's in a temporary table because it regularly gets updated).

Now I want to display both employees information in one row as output. I am able to self join, but when it comes to linking with the temporary table I get confused.

Employee Table:

Emp_ID Emp_Name Status Joined_Date
111 Jack On_Leave 01/01/2000
222 Smith Working 02/02/2000
333 Joan Working 03/03/2001
444 Emily On_Leave 04/04/2001
555 Mark Working 05/05/2002

Temporary Table:

Emp_ID_1 Emp_ID_2
111 222
222 555

Now the output I'm trying to get is:

Emp_ID_1 Emp_Name_1 Status_1 Joined_date_1 Emp_ID_2 Emp_Name_2 Status_2 Joined_date_2
111 Jack On_Leave 01/01/2000 222 Smith Working 02/02/2000
222 Smith Working 02/02/2000 555 Mark Working 05/05/2001

This is a simplified example as my actual Employee table has many additional columns to display for both Employees.

Answer Source

Self-joining is nothing magical. You're joining two tables, which just happen to be the same table, so you absolutely have to use table aliases to distinguish between them. In your case:

SELECT e1.*, e2.*
FROM   employee e1
JOIN   temp_table t ON e1.emp_id = t.emp_id_1
JOIN   employee e2 ON t.emp_id_2 = e2.emp_id

You can find additional information in the Documentation (beta) topic.