Re: Complex SQL Join

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 Fri Apr 05 2002 - 04:05:22 CST