Thread: Help with Outer Joins
I come from an Oracle background and have noted that postgres 7.1 supports outer joins...but I'm not sure of the syntax. Note below in the first AND clause the (+) next to k.permit_id, I need to get the nulls back as well as the valuebut I get an error when I use (+) SELECT t.permit_id, t.issue_date, t.issued_by, t.location, t.purpose ||' '|| t.subpurpose as spurpose, t.date_from, t.date_to,t.permit_conditions, t.other_info, k.key_code, p.person_id, p.firstname ||' '|| p.lastname as name FROM person p, forest_permit t, permit_key k WHERE p.person_id = t.person_id AND t.permit_id = k.permit_id(+) AND p.lastname LIKE 'Bloggs' AND p.firstname LIKE 'Joe' ORDER BY t.issue_date Best Regards, Sharon Cowling
Sharon, > I come from an Oracle background and have noted that postgres 7.1 > supports outer joins...but I'm not sure of the syntax. Note below in > the first AND clause the (+) next to k.permit_id, I need to get the > nulls back as well as the value but I get an error when I use (+) The (+) method of performing outer joins is an Oracle proprietary deviation from the SQL92 standard. No other database uses this. The PostgreSQL, and SQL92 standard, method for performing outer joins is to use and explicit join syntax: SELECT * FROM table_a LEFT OUTER JOIN table_b ON table_a.1 = table_b.2 Please be careful using LEFT and RIGHT outer joins to get the joined tables on the correct sides! See: http://www.postgresql.org/idocs/index.php?sql-select.html for more information on JOIN syntax. see: http://techdocs.postgresql.org/ for articles on porting from Oracle to Postgres -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco