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 .
Syed Ali Shah RashdiT24/Insight Technical ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
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,
Sum([total balance]) as [grand total]
From @Customer Left Join @customerrelation On Customernum = childcustomer
group by COALESCE([parent customer], customernum)

The idea is, the childcustomer is translated to the parent customer, In case there is no relation the customernum is a parent customer without children relations. This is what COALESCE([parent customer], customernum) does. The NULLs can come in by the left join not finding a relation.

The rest is a smple sum, but there is one precondition about this, in that the childcustomer column in customer.relation should be unique and no relation stored twice, otherwise you sum the total balance twice.

For example that would be the case, if you would add (10001,10011) twice, but also when introducing (10002,10011) the $20 are counted twice for 10001 and 10002, so pay attention to that in your customer.relation data by forcing uniqueness of childcustomer.

Bye, Olaf.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Syed Ali Shah RashdiT24/Insight Technical ConsultantAuthor Commented:
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.
0
Syed Ali Shah RashdiT24/Insight Technical ConsultantAuthor Commented:
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)
0
Olaf DoschkeSoftware DeveloperCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.