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.amount100 1000 -500200 1000 -200 But from following query we are getting:select dd.id_acc,sum(dd.amount),sum(dd1.amount)from dd left outer join dd1on dd.id=isnull(dd.parent,dd1.id)where dd.parent is nullgroup by dd.id_accid_acc dd.amount dd1.amount100 1000 -500200 2000 -200How 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 DD1Amountfrom 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.." |
|
|