🚀 go-pugleaf

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