Forum OpenACS Development: Outer Join, please help
I'm trying to port the following sql code to PostgreSQL. I can't figure out how to write the outer joins
create or replace view ec_recommendations_cats_view as select recs.*, nvl(cats.category_id,nvl(subs.category_id,subsubs.category_id)) as the_category_id, nvl(cats.category_name,nvl (subs.category_name,subsubs.category_name)) as the_category_name, nvl(subs.subcategory_id,subsubs.subcategory_id) as the_subcategory_id, nvl(subs.subcategory_name,subsubs.subcategory_name) as the_subcategory_name, subsubs.subsubcategory_id as the_subsubcategory_id, subsubs.subsubcategory_name as the_subsubcategory_name from ec_product_recommendations recs, ec_categories cats, ec_subcategories_augmented subs, ec_subsubcategories_augmented subsubs where recs.category_id = cats.category_id(+) and recs.subcategory_id = subs.subcategory_id(+) and recs.subsubcategory_id = subsubs.subcategory_id(+);
At first, I tried this:
from ((ec_product_recommendations recs left join ec_categories cats on (recs.category_id = cats.category_id)) left join ec_subcategories subs on (recs.subcategory_id = subs.subcategory_id)) left join ec_subsubcategories subsubs on (recs.subsubcategory_id = subsubs.subcategory_id)
But that doesn't seem right. How should I create the outer joins? Thanks.
You can simplify it like this:
PG nests joins left to right, so it will do the recs-cats join first, take the results of that and join with subs, etc...from ec_product_recommendations recs LEFT JOIN ec_categories cats using (category_id) LEFT JOIN ec_subcategories_augmented subs using (subcategory_id) LEFT JOIN ec_subsubcategories_augmented subsubs on (recs.subsubcategory_id = subsubs.subcategory_id);
If you feel bad about depending on left-right evaluation you can always use parenthesis. Since (a left b) left c isn't equivalent to a left (b left c) in the general case, you don't really have to worry about any SQL92-compliant RDBMS changing the evaluation order, though.
SELECT t.* into taxes FROM ec_orders o, ec_addresses a, ec_sales_tax_by_state t WHERE o.shipping_address=a.address_id AND a.usps_abbrev=t.usps_abbrev(+) AND o.order_id=v_order_id;
SELECT into taxes t.* FROM ec_orders o JOIN ec_addresses on (o.shipping_address=a.address_id) LEFT JOIN ec_sales_tax_by_state t using (usps_abbrev) WHERE o.order_id=v_order_id;