The other alternative is to use:
select AA.id, AA.seq, AA.price + coalesce(sum(BB.price),0)
from AA left outer join BB using (id, seq)
group by AA.id, AA.seq, AA.price;
The sum and group by seems more natural to me. The coalesce
takes care of the case where there is nothing in table BB,
but there is something (and you want to see it) in AA.
elein
On Saturday 19 April 2003 11:29, Feite Brekeveld wrote:
> Hi,
>
> I havbe the following SQL related question.
>
> Table AA:
>
> id, seqno, price
>
> A10 1 1000.0
>
> A10 2 2000.0
>
>
>
> Table BB:
>
>
> id seqno subseq price
>
> A10 1 1 10
>
> A10 1 2 20
>
> A10 1 3 30
>
> A10 2 1 25
>
>
> I would like to have a query that joins table AA and BB to the result:
>
> id seqno price
> A10 1 1060
> A10 2 2025
>
> Thanks,
>
> Feite Brekeveld
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
--
----------------------------------------------------------------------------------------
elein@varlena.com Database Consulting www.varlena.com
I have always depended on the [QA] of strangers.