Re: Outer Join

From: onedbguru <onedbguru_at_gmail.com>
Date: Fri, 11 Feb 2011 15:21:15 -0800 (PST)
Message-ID: <a0896ca7-1a31-46a0-86f1-bb7438a56d84_at_o8g2000vbq.googlegroups.com>

On Feb 10, 7:20 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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

I prefer the ANSI form of the query...

SELECT data..........
FROM customer c, left outer join customer_account ca on c.customer_id = ca.customer_id

                              left outer join customer_address cd on
c.customer_id = cd.customer_id
                              left outer join customer_order co
c.customer_id = co.customer_id
                              inner join order_details od on

co.order_id = od.order_id
where customer_id = 123;

Now, if you do not provide some where clause limiting the customer and providing the supporting indexes, and these are fairly large tables, this could run a very long time. Received on Fri Feb 11 2011 - 17:21:15 CST