parent n child records - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

parent n child records

Author

Topic

asad.lte
Starting Member

32 Posts

Posted - 2010-04-30 : 08:20:33

hi guys,

thnx n my question to u people is ,i hav a query which is giving output .it has 10 columns n in tht query thr r parents n childs .
for example
A is father to 1,2,3
B is father to 4,5
c is father to 6,7
my expected output wud b somthing which shud giv all parents amount in grand total.
one more thing i wud like to add here is if father amount is 100 then it shud cum from his respective childs .if v r adding all parents it shud display grand total n suppose i want to c individual amounts for the child products it shud display in tht way.hope u guys got wat my pouput is .plz help me strugglin since many days

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-04-30 : 09:39:12

Please, no text-speak on this site!! It's like asking for help but not doing anythng yourself before seeking assistance, a very lazy way to post and to seek a resolution. - IMO

Can you give the DDL of your tables (I'm guessing you have a minimum of two) so that some relatively accurate assistance can be provided? Sample data for the tables would be ideal as well. And any code you've written in your attempt to resolve the issue.

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

asad.lte
Starting Member

32 Posts

Posted - 2010-04-30 : 12:25:37

guys,

sory ,the query was too big for me to understand so i hav made a new temp table with output which i got from da big query.but i dont want to create temp table .it means i shud use da original query to get da output. i will post that query soon.
thnx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-01 : 00:42:54

seems like what you need to use is common table expression. have a look at CTEs below

http://msdn.microsoft.com/en-us/library/ms186243.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

asad.lte
Starting Member

32 Posts

Posted - 2010-05-01 : 04:46:46

hi guys,

i tried with CTE it is giving output but not as wat i expected i need all parents total as grand total and i need their respective childs from the CTE is it possible or ne other approach to solve this query . plz help me

thnx

asad.lte
Starting Member

32 Posts

Posted - 2010-05-01 : 04:48:46

hi guys,

even i tried with cube n rollup but still landed in confusion as m a beginner .ne help wud b appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-01 : 05:20:11

quote:
Originally posted by asad.lte

hi guys,

i tried with CTE it is giving output but not as wat i expected i need all parents total as grand total and i need their respective childs from the CTE is it possible or ne other approach to solve this query . plz help me

thnx

can you show data sample and then give output you want?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

asad.lte
Starting Member

32 Posts

Posted - 2010-05-01 : 05:59:59

hi guys,

m posting da data which i got after running da big query .thr r around 50000 records of this kind m just giving 10 records for ur understanding.hope this helps u

thnx

ID prod_code TRAN parentid freq tname FEE1 FEE2 fee3 parent_name Productname setupfee prorated
100847 b001 B1SA NoParent M Monthly Subscription 845 0 0 NoParent Salesmaker 2.0 NULL 0
100847 b003 B3SA NoParent M Monthly Subscription 239 0 0 NoParent Compliance 100 0
100847 bkel BKSA NoParent M Monthly Subscription 0 0 0 NoParent BookOut - Kelley Blue Book#174 NULL 0
100847 cmpl CMSA b003 M Monthly Subscription 0 0 0 Compliance DealWatch#153 NULL 0
100847 cweb WMSA b001 M Monthly Subscription 0 0 0 Salesmaker 2.0 WebsitePlus#153 NULL 0
100847 desk DLSA NoParent M Monthly Subscription 0 0 0 NoParent SalesMaker#153 0 NULL
100847 DMS DMSA NoParent M Monthly Subscription 99 0 0 NoParent DealTransfer#174 0 0
100847 inve IVSA b001 M Monthly Subscription 0 0 0 Salesmaker 2.0 BookOut Pro NULL 0
100847 redf RFSA b003 M Monthly Subscription 0 0 0 Compliance Red Flags#153 NULL 0

asad.lte
Starting Member

32 Posts

Posted - 2010-05-01 : 06:38:36

hi guys,

with id=100847 and prod_code=b001 and parent id=noparent but 1)id=100847 and prod_code=cweb is child to b001 2) id=100847 and prod_code=inve is child to b001.

in the similar manner i have parentid with noparent for some productname .

And we have id=100847 and prod_code=DMS with noparent but no childs for this ID

These are the two categories .

1) one with childs
2) Individual

hope this help u guys in giving da query.

And i need to add the fee amount of the childs with respect to his parent giving subtotal and the grand total for all products(parents).

plz help me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources