Re: Outer Join

From: joel garry <joel-garry_at_home.com>
Date: Thu, 10 Feb 2011 16:20:00 -0800 (PST)
Message-ID: <4423c1fc-c8c2-43ea-a7ba-63ead127ea87_at_s28g2000prb.googlegroups.com>

On Feb 10, 7:38 am, The Magnet <a..._at_unsu.com> wrote:
> Hi,
>
> Been trying for a couple of hours to do this.  Here is my query:
>
> SELECT data..........
> FROM customer c, customer_account ca, customer_address cd,
> customer_order co, order_details od
> WHERE c.customer_id = ca.customer_id(+)
>   AND c.customer_id = cd.customer_id(+)
>   AND c.customer_id = co.customer_id(+)
>   AND co.order_id = od.order_id;
>
> The idea is this:
> -  The customer may or many not have an account record (outer join)
> -  The customer may or may not have an address record (outer join)
> -  The customer may or may not have an order record (outer join)
>
> BUT, if the customer DOES have an order record, then he WILL have an
> order details record.
>
> Not sure how to code that.  Can anyone help?  I'm going to keep
> trying.......
>
> Thanks!

Gerard showed how to code it, but to enforce it is a check constraint, you'd have to add header and at least one detail as a single transaction before your select. For legitimate reasons, the system I work on allows orphaned headers, since there are a lot of attributes possible in both header and detail. But customer or order without an account is a nono, and I've had to do a heck of a lot of back end work to bring up a web facing system because of the differences between what a trained order person dealing with businesses can be expected to do, versus a segment of the general public on a different database and engine accessing the same back end.

jg

--
_at_home.com is bogus.
“...a galley rowed by slaves and commanded by pirates.”  Arrrrrrr...
http://www.cjr.org/the_news_frontier/aol_settled_with_unpaid_volunt.php

Received on Thu Feb 10 2011 - 18:20:00 CST