Syed Ali Shah Rashdi
asked on
parent child query in sql .
hi all,
need help with one query .
I have customer table with customer number and balances; i have another table with parent customer and its child customer linked with it , now i have to get total for parent and child against parent customer number . keeping in mind that in customer.relation parent can have multiple child.
Table = Customer .
fields = Customernum , total balance
data = 10001 , 50,000
10011 , 20,000
10012 , 30,000
Table 2 = customer.relation
Fields = parent customer , childcustomer
data = 10001 , 10011
10001, 10012
Expected result .
customernum,totalbalance
10001 , 100,000
Thanks .
need help with one query .
I have customer table with customer number and balances; i have another table with parent customer and its child customer linked with it , now i have to get total for parent and child against parent customer number . keeping in mind that in customer.relation parent can have multiple child.
Table = Customer .
fields = Customernum , total balance
data = 10001 , 50,000
10011 , 20,000
10012 , 30,000
Table 2 = customer.relation
Fields = parent customer , childcustomer
data = 10001 , 10011
10001, 10012
Expected result .
customernum,totalbalance
10001 , 100,000
Thanks .
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
this is what i did and it worked . let me know if that's fine or you have anything else in mind .
Select COALESCE([parent customer], customernum) as customernum,
Sum(cu2.[total balance]) + cu.[total balance] as [grand total]
From @Customer as cu Left Join @customerrelation On Customernum = childcustomer
Left Join @Customer as cu2 On cu2.Customernum = childcustomer
group by COALESCE([parent customer], customernum)
Select COALESCE([parent customer], customernum) as customernum,
Sum(cu2.[total balance]) + cu.[total balance] as [grand total]
From @Customer as cu Left Join @customerrelation On Customernum = childcustomer
Left Join @Customer as cu2 On cu2.Customernum = childcustomer
group by COALESCE([parent customer], customernum)
What you do is erroring, no.
Take a look at this result to see how COALESCE translates the child customers 10011 and 10012 to the parent customer 10001 you want in the overal result:
You don't want to join Customer twice, the query I gave you already results in what you wanted.
it sums all three records into one.
Maybe add some more sample data to illustrate in what aspect my solution is wrong.
Bye, Olaf.
Take a look at this result to see how COALESCE translates the child customers 10011 and 10012 to the parent customer 10001 you want in the overal result:
declare @Customer as table (Customernum int, [total balance] money)
declare @customerrelation as table ([parent customer] int, childcustomer int)
Insert Into @Customer Values (10001,$50.00),(10011,$20),(10012,$30)
Insert Into @Customerrelation Values (10001,10011),(10001,10012)
Select COALESCE([parent customer], customernum) as customernum,
[total balance]
From @Customer Left Join @customerrelation On Customernum = childcustomerYou don't want to join Customer twice, the query I gave you already results in what you wanted.
it sums all three records into one.
Maybe add some more sample data to illustrate in what aspect my solution is wrong.
Bye, Olaf.
ASKER
in customer relation table we have parent customer number as many time as its child customer e.g
Table 2 = customer.relation
Fields = parent customer , childcustomer
data = 10001 , 10011
10001, 10012
your query works but what it does is it adds parent balance 2 times or as many time as many child it has and it doesn't add balances for child.