Join on ambiguous column name
615461Dec 31 2007 — edited Jan 2 2008I have three tables I am working with. employee_passenger, nonemployee_passenger, employee (linked from another database). I want to outer join the passenger tables on the employee table for a view I am creating. Can I alias the two passenger tables and generically outer join both in one statement?
Example:
SELECT p.pk_passenger_id "Passenger_ID", n.pk_passenger_id "Passenger_ID", e.emp_id
FROM employee_passenger p, nonemployee_passenger n, employee@somewhere e
WHERE Passenger_ID = e.emp_id (+)
Desired result:
Passenger_ID Passenger_ID emp_id
-------------------- -------------------- -----------
101 9230
102
103 9249
Or would it be better to do something like:
SELECT p.pk_passenger_id, n.pk_passenger_id, e.emp_id
FROM employee_passenger p, nonemployee_passenger n, employee@somewhere e
WHERE p.pk_passenger_id IS NULL OR p.pk_passenger_id = e.emp_id