Forum OpenACS Development: Outer Join, please help

Collapse
Posted by Gilbert Wong on

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.

Collapse
Posted by Vinod Kurup on
hmmm... offhand, I would guess that your query would work. it doesn't?

You can simplify it like this:

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);
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...
Collapse
Posted by Don Baccus on
Vinod's approach is the correct one, though I didn't check to make sure the details are 100% right.

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.

Collapse
Posted by Gilbert Wong on
Great.  I'll give it a try.  Thanks guys.
Collapse
Posted by Gilbert Wong on
Ok. Here's another query. Did I port it correctly? Thanks.

Oracle

        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;

PostgreSQL

        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;
Collapse
Posted by Don Baccus on
You left out the alias "a" but other than that I think it's OK...