Re: Complex SQL Join

select a_id,b_sum,sum(c.amout)
from c,

        (select a.id a_id,sum(b.amount) b_sum from a,b where a.id=b.a_id group by a_id)
where c.a_id=a.id
group by a_id,b_sum
havinf sum(c.amount)>b_sum

"Nick P" <nick_pope_at_nospamhotmail.com> wrote in message news:uaqtldljvdpcfd_at_corp.supernews.com...
> Urm... where's the complex bit?
>
> select a.id
> ,sum(b.amount)
> ,sum(c.amount)
> from a, b, c
> where a.id = b.a_id -- where b.a_id is the foreign key to a.id
> and a.id = c.a_id
> group by a.id
> having sum(c.amount) > sum(b.amount)
>
>
> "Robert Blomstrand" <rbl_at_qmedia.co.za> wrote in message
> news:3cad7288$0$233_at_hades.is.co.za...
> > Can anyone tell me what the best method would be to achieve the
following:
> >
> > 3 tables called A, B & C. There is a one to many relationship between A
&
> B
> > and also a one to many relationship between A & C. So for every A entry
> > there will be one or more corresponding B entries and for every A entry
> > there will be 0 (!) or more C entries.
> >
> > Tables B & C both have an amount field. For each entry in A, I want to
sum
> > up all of the corresponding B amounts and all of the corresponding C
> > amounts, but I only want to return an entry if the sum of all C amounts
> > exceed the sum of all B amounts.
> >
> > Sample data:
> >
> > A Table B Table C Tables
> > ===== ===== ======
> > ID ID AMT ID AMT
> > -- --- ------ -- ------
> > 1 1 100 1 100
> > 1 100
> >
> > 2 2 100 2 100
> > 2 100
> > 3 3 100
> >
> > In the above example I expect to only return the following:
> > ID Total B Total C
> > 2 100 200
> >
> > Regards.
> >
> >
>
>
Received on Sun Apr 07 2002 - 12:51:18 CDT