Re: select last time for ordering - complex sql

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Wed, 28 Apr 2010 00:12:39 -0700 (PDT)
Message-ID: <c081962a-ffe3-4e82-8760-0516fd75fbac_at_g11g2000yqe.googlegroups.com>

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? Cheers.

Carlos. Received on Wed Apr 28 2010 - 02:12:39 CDT