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.
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
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
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.