SQL TREE - 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

SQL TREE

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2011-11-22 : 00:57:18

HI TEAM,

i have a table where id and parentid are cols.

id parentid

1 null

2 1

3 2

4 3

5 4

6 5

i need to get id 6 even i select 1,2,3 ,4 or 5 in my select list.

pls suggest the best way .

challenge everything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 01:36:54

use a recursive CTE

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

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

pnpsql
Posting Yak Master

246 Posts

Posted - 2011-11-22 : 01:52:26

is this possible without cte.

challenge everything

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-22 : 02:34:14

Use common table expression

Check below this will give you an idea

declare @t table (id VARCHAR(max), parent VARCHAR(max))
insert @t (id, parent) values (1, null), (2,1), (3,2), (4,3), (5,4), (6,5)

; with cte as (
select id, parent
, id Path
from @t
where parent is null
union all
select child.id, child.parent
, parent.Path + ' , ' + child.id as Path
from @t child
join cte parent
on parent.id = child.parent
)
select *
from cte
order by
Path

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-22 : 02:34:46

check this family tree also
http://www.sqlservercurry.com/2009/06/simple-family-tree-query-using.html

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 03:13:24

quote:
Originally posted by pnpsql

is this possible without cte.

challenge everything

yes its possible, but you need a looping logic for that

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

pnpsql
Posting Yak Master

246 Posts

Posted - 2011-11-22 : 08:10:41

i need to get only last child ..pls help with simple query

challenge everything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 09:49:26

quote:
Originally posted by pnpsql

i need to get only last child ..pls help with simple query

challenge everything

simplest approach is to use cte as suggested above

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

pnpsql
Posting Yak Master

246 Posts

Posted - 2011-11-22 : 23:14:15

hi team, is there any site or eBook available to read the basics of recursive cte, i used only simple cte for duplicate finding. pls suggest.

challenge everything

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 23:43:07

quote:
Originally posted by pnpsql

hi team, is there any site or eBook available to read the basics of recursive cte, i used only simple cte for duplicate finding. pls suggest.

challenge everything

Did you even have look at link i posted
It has an example of recursive CTE with detailed explanation of how it works

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources