Re: Join Question

From: Anup Jalan <anup.jalan_at_rave-tech.com>
Date: 28 Jan 2003 04:12:57 -0800
Message-ID: <532afd4.0301280412.7dbff341_at_posting.google.com>

Try

SELECT  a.id           as x,
        a.amount       as y,
        sum (b.amount) as z
  FROM  table1 a,
        table2 b

  WHERE a.id = = b.id (+)
  GROUP BY a.id,

           a.amount
/

Anup Jalan
Rave Technologies
www.rave-tech.com

DiggidyMack69_at_hotmail.com (DiggidyMack69) 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 Tue Jan 28 2003 - 13:12:57 CET