How to Query Parent/Child/Grandchild ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to Query Parent/Child/Grandchild ?

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2011-07-13 : 16:50:38

I'm wondering how it is possible (and forgive my potential for improper terminology) to "subquery" for related data. To better explain, I figured I would illustrate my desires instead of trying to do so through mere text. What I would like to do is to be able to select the top-level parent (Folder 1) and have it select data for the children of it's child folders (see graphic below).

Imagine I have the following tables:


tb_applications		
f_ID	f_parentID	f_type		f_name
1	0		group		Folder 1
2	1		group		Folder 2
3	1		group		Folder 3
4	2		computer	Computer A
5	2		computer	Computer B
6	3		computer	Computer C
7	3		computer	Computer D

The above table illustrated looks like this:

The data desired is in the following table:


tb_applicationusage		
f_computername	f_application	f_runtime
Computer A	excel		5
Computer A	word		10
Computer B	excel		5
Computer B	powerpoint	10
Computer C	outlook		5
Computer C	word		10
Computer D	powerpoint	5
Computer D	outlook		10

The desired output then, is:

				
f_application	RunTime			
excel		10			
word		20			
powerpoint	15			
outlook		15

For reference, my existing query is here, but is lacking a serious segment:

SELECT f_application, SUM(f_runtime) AS Runtime
 FROM tb_applicationusage au
 LEFT OUTER JOIN tb_applications app ON au.f_application = app.f_applicationID
 WHERE ?????????????????????
 GROUP BY f_application

The problem I have is understanding with my limited knowledge of SQL how Computer A is actually related to the top level folder (Folder 1). I'm trying to self-teach myself SQL, but books can't cover some of the stuff I'm trying to play with. Searches here and on various search engines haven't helped me accomplish this, but that may be entirely because I can't find the proper terminology.

EDIT: I should note that the number of child folders is potentially (though not realistically) infinite. There may very well be folders five levels deep in a real-world scenario.

Thanks very much in advance,
Matt

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-13 : 17:21:16

Not sure what you're after in regards to your desired output. But to answer your question about SELECTing from a hierarchy here is one way (using a recursive CTE)


with tb_applications	(f_ID,	f_parentID, f_type, f_name)
as     (
       select 1,	0,		'group'	,'Folder 1' union all
       select 2,	1,		'group'	,'Folder 2' union all
       select 3,	1,		'group'	,'Folder 3' union all
       select 4,	2,		'computer'	,'Computer A' union all
       select 5,	2,		'computer'	,'Computer B' union all
       select 6,	3,		'computer'	,'Computer C' union all
       select 7,	3,		'computer'	,'Computer D'
)


, rCTE as (
select a.f_id
       ,a.f_parentid
       ,a.f_type
       ,a.f_name
       ,0 as [lev]
       ,convert(varchar(30), '0_' + convert(varchar(10), f_id)) lineage
from   tb_applications a
where f_parentid = 0 --ultimate parents

UNION ALL
--recusive portion
select a.f_id
       ,a.f_parentid
       ,a.f_type
       ,a.f_name
       ,c.[lev] + 1
       ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_id))
from   rCTE c
join   tb_applications a
       on a.f_parentid = c.f_id
)

select lineage
       ,replicate('   ', [lev]) + f_name [hierarchy]
from rCTE
order by lineage

output:
lineage                        hierarchy
------------------------------ ---------------------
0_1                            Folder 1
0_1_2                             Folder 2
0_1_2_4                              Computer A
0_1_2_5                              Computer B
0_1_3                             Folder 3
0_1_3_6                              Computer C
0_1_3_7                              Computer D

Be One with the Optimizer
TG

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2011-07-13 : 17:38:19

TG, thanks for the expansive reply. The desired output would be below:

The desired output is as follows. It would allow me to select the total application runtime of all children of the selected item. In this case, if I choose "Folder 1", I need the runtimes of all the applications of the children, as shown below. This works fine if choose "Folder 2" or "Folder 3", because I my WHERE statement is "WHERE f_parentID = x". Unfortunately, it isn't that simple if you choose "Folder 1"

				
f_application	RunTime
excel		10			
word		20			
powerpoint	15			
outlook		15

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-13 : 23:43:45

I'm not at a computer with sql server so I can't test this but I think all you need to do is replace the final SELECT (above) with this:

EDIT:
Corrected code now that I could test it:


select  au.f_application
	,sum(f_runtime) [RunTime]
from rCTE c
join tb_applicationusage au
	on au.f_computername = c.f_name
group by au.f_application

OUTPUT:
f_application RunTime
------------- -----------
excel         10
outlook       15
powerpoint    15
word          20

Then replace this WHERE clause in rCTE:
where f_parentid = 0 --ultimate parents

with this:
where f_id = <whichever ID you want to start with>

Be One with the Optimizer
TG

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2011-07-14 : 10:32:26

Thanks TG, the help is greatly appreciated. This is definitely way over my head. I tested your first one and it worked to generate what you said it would. How to make it use live data and use a 3-table join (how I actually have it) is something I will have to play with another day once I can wrap my head around this.

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2011-07-14 : 10:48:48

Sorry, I missed your updated post. I tested that and it does work, I think I understand why as well.

So does the following portion of your original query have to be statically written in the query?:


with tb_applications	(f_ID,	f_parentID, f_type, f_name)
as     (
       select 1,	0,		'group'	,'Folder 1' union all
       select 2,	1,		'group'	,'Folder 2' union all
       select 3,	1,		'group'	,'Folder 3' union all
       select 4,	2,		'computer'	,'Computer A' union all
       select 5,	2,		'computer'	,'Computer B' union all
       select 6,	3,		'computer'	,'Computer C' union all
       select 7,	3,		'computer'	,'Computer D'
)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-14 : 10:52:00

Your welcome.
Assuming your posted schemas were accurate then this should work as is. The first CTE (common table expression) was just to create and populate your existing table so I could test my code.


;with rCTE as (
       select a.f_id
              ,a.f_parentid
              ,a.f_type
              ,a.f_name
              ,0 as [lev]
              ,convert(varchar(30), '0_' + convert(varchar(10), f_id)) lineage
       from   tb_applications a
       where f_id = 1 

       UNION ALL
       --recusive portion
       select a.f_id
              ,a.f_parentid
              ,a.f_type
              ,a.f_name
              ,c.[lev] + 1
              ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_id))
       from   rCTE c
       join   tb_applications a
              on a.f_parentid = c.f_id
)
--final select
select  au.f_application
	,sum(f_runtime) [RunTime]
from rCTE c
join tb_applicationusage au
	on au.f_computername = c.f_name
group by au.f_application

Be One with the Optimizer
TG

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2011-07-14 : 11:13:34

TG, you are a SQL genius. Seriously. All the research I did outside of this post has semi-coherent answers that were literally dozens of lines longer than what you had, and nothing else had a solution as elegant.

Now off to my next portion: joining to a 3rd table that references the application name to a real application. So currently, you see "powerpoint = xxxxx", but I have another table that references "powerpoint" to "Microsoft PowerPoint" so that the results are prettier. I'm not asking for anymore help here yet, I feel like I've won the knowledge lottery for today, and I don't want to jinx it.

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2011-07-14 : 11:46:52

Works like a champ. Your help is appreciated more than I can elaborate here:


--final select
select apps.f_applicationname
	,sum(f_runtime) [RunTime]
from rCTE c
join tb_applicationusage au
	on au.f_computername = c.f_itemname
JOIN tb_applications apps 
	ON au.f_application = apps.f_applicationID
group by apps.f_applicationname, au.f_application

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-14 : 15:00:21

No prob - glad I could help.
In case you couldn't tell the [lev] and lineage columns are not needed for this. They were there so you could see what the recursion part was doing and to create that initial output (from my first post).

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources