Get all children tree query

[kloner]

Hi all,

I am pulling my hair out here! I am trying to select all the children records from the following data (i.e. I need to return all of the data you see below.)

The only 'where clause' data is have is WHERE ID = 37

[ID]  [ParentID]  [Title]
 37      1         BIG GIG 
 38      37        2004 News and Information 
 39      37        Merchandise 
 40      37        Previous Events 
 45      40        2003 - Wembley Arena, London

I have been trying to do a recursive query .... with no luck.

SELECT M2.*
FROM TBL_SectionsTree AS M1
   JOIN TBL_SectionsTree AS M2
	ON M1.SectionID = M2.ParentID
WHERE
	M1.Sectionid = 37 or M1.ParentID = M1.SectionID

This query i need would have been done a million times (i.e. getting all the messages under a thread on a forum), however I am strugging.

Thanks for any tips or suggestions or queries!!

Matt

kloner

 

[JohnDTampaBay]

Hierarchies can be painful to navigate in SQL Server. Here's an article from SQL Magazine that describes a self-maintaining system for hierarchies. It takes a little work to set up, but in the end it all pays off. Good luck!

http://www.sqlmag.com/Articles/Print.cfm?ArticleID=8826

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[r937]

there is no such thing as a recursive query in sql server

you have to code as many self-joins as levels that you want to go down

here's the query for children and grandchildren:

[tt]select M2.*
from TBL_SectionsTree as M1
left outer
join TBL_SectionsTree as M2
on M1.SectionID = M2.ParentID
where M1.Sectionid = 37
union all
select M3.*
from TBL_SectionsTree as M1
inner
join TBL_SectionsTree as M2
on M1.SectionID = M2.ParentID
left outer
join TBL_SectionsTree as M3
on M2.SectionID = M3.ParentID
where M1.Sectionid = 37 [/tt]

rudy

http://r937.com/

SQL Consulting

 

[kloner]

Thanks Rudy, however, would a query like this drain the CPU? Is there a way to create a SP and call itself.

What happens if I have 10 levels? I just keep building up the query do i?

kloner

 

[r937]

10 levels? nothing wrong with a 10-deep self-join

you could write a stored proc to do the recursion, but there's no guarantee it will be any faster

unless you are documenting the reporting hierarchy of a bloated government department, is it really necessary to have 10 levels?

few real life applications come to mind where the number of levels in practice exceed 10



rudy

http://r937.com/

SQL Consulting

 

[kloner]

Ok, I have 9 levels at the moment .... it's a website struction, lots of sections and information.


I have created the query as such, which seems to be fine. I just thought it could be shortened a little ...


select
DISTINCT M2.SectionID
from
TBL_SectionsTree as M1 left outer join
TBL_SectionsTree as M2 on M1.SectionID = M2.ParentID left outer join
TBL_SectionsTree as M3 on M2.SectionID = M3.ParentID left outer join
TBL_SectionsTree as M4 on M3.SectionID = M4.ParentID left outer join
TBL_SectionsTree as M5 on M4.SectionID = M5.ParentID left outer join
TBL_SectionsTree as M6 on M5.SectionID = M6.ParentID left outer join
TBL_SectionsTree as M7 on M6.SectionID = M7.ParentID left outer join
TBL_SectionsTree as M8 on M7.SectionID = M8.ParentID left outer join
TBL_SectionsTree as M9 on M8.SectionID = M9.ParentID

where
M1.Sectionid =6

union all

select
DISTINCT M3.SectionID
from
TBL_SectionsTree as M1 left outer join
TBL_SectionsTree as M2 on M1.SectionID = M2.ParentID left outer join
TBL_SectionsTree as M3 on M2.SectionID = M3.ParentID left outer join
TBL_SectionsTree as M4 on M3.SectionID = M4.ParentID left outer join
TBL_SectionsTree as M5 on M4.SectionID = M5.ParentID left outer join
TBL_SectionsTree as M6 on M5.SectionID = M6.ParentID left outer join
TBL_SectionsTree as M7 on M6.SectionID = M7.ParentID left outer join
TBL_SectionsTree as M8 on M7.SectionID = M8.ParentID left outer join
TBL_SectionsTree as M9 on M8.SectionID = M9.ParentID
where
M1.Sectionid = 6


union all

select
DISTINCT M4.SectionID
from
TBL_SectionsTree as M1 left outer join
TBL_SectionsTree as M2 on M1.SectionID = M2.ParentID left outer join
TBL_SectionsTree as M3 on M2.SectionID = M3.ParentID left outer join
TBL_SectionsTree as M4 on M3.SectionID = M4.ParentID left outer join
TBL_SectionsTree as M5 on M4.SectionID = M5.ParentID left outer join
TBL_SectionsTree as M6 on M5.SectionID = M6.ParentID left outer join
TBL_SectionsTree as M7 on M6.SectionID = M7.ParentID left outer join
TBL_SectionsTree as M8 on M7.SectionID = M8.ParentID left outer join
TBL_SectionsTree as M9 on M8.SectionID = M9.ParentID

where
M1.Sectionid = 6


union all

select
DISTINCT M5.SectionID
from
TBL_SectionsTree as M1 left outer join
TBL_SectionsTree as M2 on M1.SectionID = M2.ParentID left outer join
TBL_SectionsTree as M3 on M2.SectionID = M3.ParentID left outer join
TBL_SectionsTree as M4 on M3.SectionID = M4.ParentID left outer join
TBL_SectionsTree as M5 on M4.SectionID = M5.ParentID left outer join
TBL_SectionsTree as M6 on M5.SectionID = M6.ParentID left outer join
TBL_SectionsTree as M7 on M6.SectionID = M7.ParentID left outer join
TBL_SectionsTree as M8 on M7.SectionID = M8.ParentID left outer join
TBL_SectionsTree as M9 on M8.SectionID = M9.ParentID

where
M1.Sectionid = 6


union all

select
DISTINCT M6.SectionID
from
TBL_SectionsTree as M1 left outer join
TBL_SectionsTree as M2 on M1.SectionID = M2.ParentID left outer join
TBL_SectionsTree as M3 on M2.SectionID = M3.ParentID left outer join
TBL_SectionsTree as M4 on M3.SectionID = M4.ParentID left outer join
TBL_SectionsTree as M5 on M4.SectionID = M5.ParentID left outer join
TBL_SectionsTree as M6 on M5.SectionID = M6.ParentID left outer join
TBL_SectionsTree as M7 on M6.SectionID = M7.ParentID left outer join
TBL_SectionsTree as M8 on M7.SectionID = M8.ParentID left outer join
TBL_SectionsTree as M9 on M8.SectionID = M9.ParentID
where
M1.Sectionid = 6

union all

select
DISTINCT M7.SectionID
from
TBL_SectionsTree as M1 left outer join
TBL_SectionsTree as M2 on M1.SectionID = M2.ParentID left outer join
TBL_SectionsTree as M3 on M2.SectionID = M3.ParentID left outer join
TBL_SectionsTree as M4 on M3.SectionID = M4.ParentID left outer join
TBL_SectionsTree as M5 on M4.SectionID = M5.ParentID left outer join
TBL_SectionsTree as M6 on M5.SectionID = M6.ParentID left outer join
TBL_SectionsTree as M7 on M6.SectionID = M7.ParentID left outer join
TBL_SectionsTree as M8 on M7.SectionID = M8.ParentID left outer join
TBL_SectionsTree as M9 on M8.SectionID = M9.ParentID

where
M1.Sectionid = 6



union all

select
DISTINCT M8.SectionID
from
TBL_SectionsTree as M1 left outer join
TBL_SectionsTree as M2 on M1.SectionID = M2.ParentID left outer join
TBL_SectionsTree as M3 on M2.SectionID = M3.ParentID left outer join
TBL_SectionsTree as M4 on M3.SectionID = M4.ParentID left outer join
TBL_SectionsTree as M5 on M4.SectionID = M5.ParentID left outer join
TBL_SectionsTree as M6 on M5.SectionID = M6.ParentID left outer join
TBL_SectionsTree as M7 on M6.SectionID = M7.ParentID left outer join
TBL_SectionsTree as M8 on M7.SectionID = M8.ParentID left outer join
TBL_SectionsTree as M9 on M8.SectionID = M9.ParentID

where
M1.Sectionid = 6



union all

select
DISTINCT M9.SectionID
from
TBL_SectionsTree as M1 left outer join
TBL_SectionsTree as M2 on M1.SectionID = M2.ParentID left outer join
TBL_SectionsTree as M3 on M2.SectionID = M3.ParentID left outer join
TBL_SectionsTree as M4 on M3.SectionID = M4.ParentID left outer join
TBL_SectionsTree as M5 on M4.SectionID = M5.ParentID left outer join
TBL_SectionsTree as M6 on M5.SectionID = M6.ParentID left outer join
TBL_SectionsTree as M7 on M6.SectionID = M7.ParentID left outer join
TBL_SectionsTree as M8 on M7.SectionID = M8.ParentID left outer join
TBL_SectionsTree as M9 on M8.SectionID = M9.ParentID

where
M1.Sectionid = 6

kloner

 

[r937]

you don't need DISTINCT and most of the LEFT OUTER joins should be INNER (see my sample for grandchildren)

rudy

http://r937.com/

SQL Consulting