asked on 10/23/2015

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 .

Microsoft SQL Server

Last Comment

Olaf Doschke

10/25/2015

ASKER CERTIFIED SOLUTION

Olaf Doschke

10/24/2015

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Syed Ali Shah Rashdi

10/25/2015

ASKER

hi thanks for help .
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.

Syed Ali Shah Rashdi

10/25/2015

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)

Olaf Doschke

10/25/2015

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:

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 = childcustomer

Open in new window

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.