Re: Select statement child - parent

From: Turkbear <john.greco_at_dot.state.mn.us>
Date: Fri, 05 Sep 2003 13:00:24 -0500
Message-ID: <mejhlvobua9n9stbsmt74u9k6b66vh4hav@4ax.com>

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 - 13:00:24 CDT