Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 query problem

Author  Topic 

rubs_65
Posting Yak Master

144 Posts

Posted - 2003-10-15 : 21:44:06
Hi,

This is the scenario:

create table dd(id_acc int,id int,parent int,amount int)

create table dd1(id_acc int,id int,parent int,amount int)

insert into dd values(100,1,null,1000)
insert into dd values(100,2,1,300)
insert into dd values(100,3,1,300)
insert into dd values(200,4,null,1000)
insert into dd values(200,5,4,300)
insert into dd values(200,6,4,300)

insert into dd1 values(100,1,null,-500)
insert into dd1 values(200,5,4,-100)
insert into dd1 values(200,6,4,-100)

We want the result like:

id_acc,dd.amount,dd1.amount

100 1000 -500
200 1000 -200



But from following query we are getting:

select dd.id_acc,sum(dd.amount),sum(dd1.amount)
from dd left outer join dd1
on dd.id=isnull(dd.parent,dd1.id)
where dd.parent is null
group by dd.id_acc

id_acc dd.amount dd1.amount

100 1000 -500
200 2000 -200

How to modify the query to get required results?

Thanks
--rubs

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-10-15 : 22:09:46
I have no idea what you are doing but does this work?


Select dd.id_acc,(Select sum(Amount) from dd as Z where Z.parent is null and dd.id_acc = Z.id_acc) as DdAmount,
sum(dd1.amount) as DD1Amount
from dd left JOIN dd1 on (dd.id = dd1.id or dd.id = dd.parent)
group by dd.id_acc


DavidM

"SQL-3 is an abomination.."
Go to Top of Page
   

- Advertisement -