outer join on more than 2 tables 1

[Julzzey]

Hi,

I'm converting from Oracle to Postgresql and I'm trying to do an outer join on 3 tables and then I also have some other where clauses. Here's the query in Oracle

select c.*,s.session_label, (u.first_names||' '||u.last_name) as admin_updater,
(u2.first_names||' '||u2.last_name) as contact_user,
pts.email as contact_email,
null as authors
from ss_papers c, ss_sessions s, persons u, persons u2, parties pts
where c.session_id = s.session_id and
c.admin_update_by = u.person_id(+) and
c.contact_user_id = u2.person_id(+) and
c.contact_user_id = pts.party_id(+) and
c.paper_id =aper_id

I can find all sorts of documentation on using outer joins for two tables, but how do you do more? Do I need to declare an inner join for my other 2 where clauses? Any help would be appreciated. Thanks!!

 

[stoolpigeon]

PostgreSQL uses ansi sql92 standard sql for joins. I have been in discussions on this where Oracle folks get pretty bent out of shape- let me say I am the messenger only.

An example of a standard compliant query with more than one outer join would be this:

SELECT customers.customernumber, orders.amount, items.description
FROM customers LEFT OUTER JOIN orders ON
(customers.customernumber = orders.customernumber)
LEFT OUTER JOIN items ON (orders.itemnumber = items.itemnumber)

More verbose than putting a + here or there- but it is the standard that was chosen to do away with the inherent ambiguity of old style joins.