Re: select last time for ordering - complex sql

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Wed, 28 Apr 2010 02:29:05 -0700 (PDT)
Message-ID: <83718f7e-347d-4c0f-89b2-a85e44c42f18_at_c36g2000yqm.googlegroups.com>

On Apr 28, 10:48 am, jodleren <sonn..._at_hot.ee> wrote:
> On Apr 28, 10:12 am, Carlos <miotromailcar..._at_netscape.net> wrote:
>
>
>
> > On Apr 27, 5:49 pm, jodleren <sonn..._at_hot.ee> wrote:
>
> > > Hi all
>
> > > Basically I have this, which gives me a tree of the product and
> > > subasseblies.
>
> > > SELECT level, stuff from products
> > > CONNECT BY PRIOR products.subpart = products.product
> > > START WITH products = 'productname'
>
> > > Now, they want to know delevery times for the last time when the parts
> > > were ordered, this goes like:
>
> > > select ordertable.orderdata, delirverytable.deliverydate
> > > from ordertable, delirverytable
> > > where ordertable.orderno = delirverytable.orderno
> > > and delirverytable.product='Something'
> > > and delivery date in (select max(delirverytable2.deliverydate) from
> > > delirverytable2 where
> > > delirverytable2.product = delirverytable.product)
>
> > > basically - I get the latest date for delivery (if present) in order
> > > to get one row only.
>
> > > Now, can I mix all this together?
> > > The point is, that some parts might never have been ordered, so they
> > > are not in - at least one of - the order and delivery tables.
>
> > > WBR
> > > Sonncih
>
> > "The point is, that some parts might never have been ordered, so they
> > are not in - at least one of - the order and delivery tables."
>
> > OUTER JOIN?
>
> But with 3 queries mixed?
>
> That is where I get lost?

(RECURSIVE SELECT) OUTER JOIN (ORDER/DELIVERY SELECT) ON PRODUCT ? Cheers.

Carlos. Received on Wed Apr 28 2010 - 04:29:05 CDT