Re: Join Question

select a.id x, a.amount y, b.amount z
from table1 a, (select id, amount from table2 group by id) b where a.id = b.id

"DiggidyMack69" <DiggidyMack69_at_hotmail.com> wrote in message news:c86ce4f.0301241104.5f96a423_at_posting.google.com...
> Hello folks,
> I am trying to figure out if there is a way to accomplish the join
> below without doing a subquery of table2. Can someone please show me
> what I am doing wrong? Is there another type of join using another
> operator besides the "(+)"?
>
> When I try;
>
> SELECT
> (a.id)as x,
> sum(a.amount)as y,
> sum(b.amount)as z
> FROM table1 a, table2 b
> WHERE a.id = b.id (+)
> GROUP BY a.id
>
>
> Results:
> x y z
> 10 400 100
> 20 225 75
>
> Desired Results:
> x y z
> 10 100 100
> 20 75 75
>
>
> table1
>
> id amount
> 10 100
> 20 75
>
>
> table2
>
> id amount
> 10 25
> 10 25
> 10 25
> 10 25
> 20 25
> 20 25
> 20 25
>
>
> Thanks in advance!
> DM
Received on Fri Jan 24 2003 - 23:14:20 CET