Re: Outer Join
Date: Fri, 11 Feb 2011 15:21:15 -0800 (PST)
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.
> _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...
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 onco.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