Sum two tables with HAVING

I would like to get sums from two tables like this:

select
   t1.id, t1.description,
   (select sum(t2.amount) from t2 where t2.id = t1.id) as sum1,
   (select sum(t3.amount) from t3 where t3.id = t1.id) as sum2
from t1

with a condition to eliminate all rows with zeroes:

where sum1 <> 0 and sum2 <> 0 --> doesn't work
*or*
having sum1 <> 0 and sum2 <> 0 --> doesn't work


How could I prevent getting rows with zeroes?


I also tried rewriting the query like this:

select
   t1.id, t1.description,
   sum(t2.amount) as sum1,
   sum(t3.amount) as sum2
from t2
   left join t1 on t1.id = t2.id
   left join t3 on t3.id = t2.id
group by
   t1.id, t1.description
having
   sum(t2.amount) <> 0 and sum(t3.amount) <> 0

This eliminates all rows with double zeroes, but all sums are twice as 
high, because of the double joins, so all rows are twice in the joined 
set that is summed.

Who can help me to get the correct query?

Thanks in advance!

Uzytkownik "P.G. Willemsen" <peter@remove.this.vhep.and.this.nl>
napisal w 
wiadomosci news:47d1690f$1@newsgroups.borland.com...
>I would like to get sums from two tables like this:
>
> select
>   t1.id, t1.description,
>   (select sum(t2.amount) from t2 where t2.id = t1.id) as sum1,
>   (select sum(t3.amount) from t3 where t3.id = t1.id) as sum2
> from t1
>
> with a condition to eliminate all rows with zeroes:
>
> where sum1 <> 0 and sum2 <> 0 --> doesn't work
> *or*
> having sum1 <> 0 and sum2 <> 0 --> doesn't work
>
>
> How could I prevent getting rows with zeroes?
>
>
> I also tried rewriting the query like this:
>
> select
>   t1.id, t1.description,
>   sum(t2.amount) as sum1,
>   sum(t3.amount) as sum2
> from t2
>   left join t1 on t1.id = t2.id
>   left join t3 on t3.id = t2.id
> group by
>   t1.id, t1.description
> having
>   sum(t2.amount) <> 0 and sum(t3.amount) <> 0
>
> This eliminates all rows with double zeroes, but all sums are twice as 
> high, because of the double joins, so all rows are twice in the joined set

> that is summed.
>
> Who can help me to get the correct query?
>
> Thanks in advance!
> Peter

slow but work

select
   t1.id, t1.description,
   (select sum(t2.amount) from t2 where t2.id = t1.id) as sum1,
   (select sum(t3.amount) from t3 where t3.id = t1.id) as sum2
 from t1
where
 (select sum(t2.amount) from t2 where t2.id = t1.id) <> 0
and
(select sum(t3.amount) from t3 where t3.id = t1.id)<> 0

Karol Bieniaszewski