Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Join on ambiguous column name

615461Dec 31 2007 — edited Jan 2 2008
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 30 2008
Added on Dec 31 2007
3 comments
1,196 views