Re: Select statement child - parent

It worked, thanks a lot.

On Fri, 05 Sep 2003 13:00:24 -0500, Turkbear <john.greco_at_dot.state.mn.us> wrote:

>Yngve <msn_at_langerud.org> wrote:
>
>>
>>
>>In my example I have database with two tables:
>>
>>customer contains the columns cust_id, name, address
>>
>>sales contains the columns sales_id, date, value, text
>>
>>When I do a
>>
>>select
>> c.name, s.date
>>from
>> customer c, sales s
>>where
>> c.cust_id=s.cust_id and
>> s.date = (select
>> max(date)
>> from
>> sales ss
>> where
>> ss.cust_is=c.cust_id
>> )
>>
>>
>>it returns all customer name and last salesdate if they have any
>>childrecord in tables "sales" like this:
>>
>>c.name s.date
>>bush 03/03/2003
>>jameson 02/01/2003
>>More 07/06/2002
>>
>>So far, so good. But is it possible with a single query to return ALL
>>customers from table customer (indepent however they have child or
>>not) and last date if there are a child present like this:
>>
>>c.name s.date
>>Bush 03/03/2003
>>Nicholson
>>Jameson 02/01/2003
>>More 07/06/2002
>>Mohammad
>>
>>
>>Best regards
>>
>>Yngve
>>
>>
>Yes..( Ok, I guess you want to know how as well):
>
>Using an Outer Join and an OR clause should do it..
>Maybe this: ( altho the subquery may cause problems...May need some tweeking..
>---------------------------------------------------------------------------------------------------------
>select
> c.name, s.date
>from
> customer c, sales s
>where
>c.cust_id=s.cust_id(+)
>and
>(
>s.date = (select max(date) from sales ss
> where
> ss.cust_is=c.cust_id
> )
> OR
> s.date Is NULL
>)
>-------------------------------------------------------------------------------------------------------------
>
>Have fun..
Received on Fri Sep 05 2003 - 16:36:27 CDT