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!

LEFT OUTER JOIN multiple tables - using the 9i syntax

RichardSquiresDec 5 2005 — edited Dec 5 2005
I've always written my queries using the (+) operator for outer joins. I want to start using the new ANSI standard available in 9i. I can do it when I'm joining two tables in a simple query, but how does it work when I am joining multiple tables?

Here is an example of some SQL that works with the (+) outer join syntax. How can I convert this to use the LEFT OUTER JOIN syntax?

SELECT *
FROM audit_entry aue,
audit_table aut,
audit_statement aus,
audit_row aur,
audit_row_pkey aup1,
audit_row_pkey aup2
WHERE aue.audit_entry_id = aus.audit_entry_id
AND aut.table_name = 'TEST_AUDITING'
AND aut.table_owner = 'CLA_JOURNAL'
AND aus.audit_table_id = aut.audit_table_id
AND aur.audit_statement_id (+) = aus.audit_statement_id
AND aup1.audit_row_id (+) = aur.audit_row_id
AND aup1.pk_column_name (+) = 'TEST_AUDTING_PK_1'
AND aup2.audit_row_id (+) = aur.audit_row_id
AND aup2.pk_column_name (+) = 'TEST_AUDITING_PK_2'

I can join audit_statement to audit_entry easy enough, but then I want to join audit_table to audit_statement, how do I do that, do I start nesting the join statements?

Thanks

Richard
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 2 2006
Added on Dec 5 2005
4 comments
39,959 views