Re: trouble with joins

Tried it, same result, in this particular case b.column2 has only one row, and it returns the value of that row multiplied by the number of rows in a.column2, which I dont understand because querying sum (a.column2) inly returns one row.

I know this is something simple, but it is escaping me right now!

select a.column1,
nvl(sum(b.column2),0),
sum(a.column2)
from table1 a, table2 b
where a.column1 = b.column1(+)
and a.column1 = 'criteria'
group by a.column1

results are:

a.column1     nvl(sum(b.column2),0)     sum(a.column2)
---------     ---------------------     --------------
OK            count of a.col2 x b.col2   OK

Moving the (+) to a.column1 returns the same result.

In article <93djtm$hn9$1_at_nnrp1.deja.com>,   Ethan Post <epost1_at_my-deja.com> wrote:
> Try...
>
> select a.column1, sum(a.column2), sum(nvl(b.column2))
> from
> table1 a,
> table2 b
> where
> a.column1 = b.column1(+)
> group by
> a.column1
>
> -Ethan
> www.freetechnicaltraining.com
>
> In article <93dit7$gov$1_at_nnrp1.deja.com>,
> Glen A. Stromquist <glenstr_at_my-deja.com> wrote:
> > I have two tables, table A has 62 rows table B has 1, (but could be
> > more), both share a common column.
> >
> > I want my query to show a sum of all of the rows in table A, and the
> > sum of the row or rows in table B on the same row.
> >
> > The join column is neither of these columns, but is my "group by"
> > column.
> >
> > whats the best way to do this?
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Tue Jan 09 2001 - 10:10:04 CST