Parent Children relationship query

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 Children relationship query

Author

Topic

Thiyagu_04
Starting Member

37 Posts

Posted - 2010-03-23 : 02:38:45

Following Shows my table stucture.I need to find who is eligible for grand children.

ID NAME ParentID
1 First NULL
2 Second NULL
3 First101 1
4 First102 1
5 First201 3
6 First202 4
7 Second101 2
8 Second201 7
9 first301 5

Thanks in advance

Sachin.Nand

2937 Posts

Posted - 2010-03-23 : 02:44:59

Have look at recursive queries in BOL.

PBUH

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-03-23 : 02:53:44

Check this below link..

http://karthik4identity.blogspot.com/2010/01/recursive-queries-replacement-to.html

Karthik
http://karthik4identity.blogspot.com/

Thiyagu_04
Starting Member

37 Posts

Posted - 2010-03-23 : 08:46:13

Thanks for the reply.
Karthik by this how to find how many level of grand childrens they have.
For eg

ID Level
1 3(this is No of level not children)
2 2
3 2
4 1
5 1
6 0
7 1
8 0
Like this i want is it possible using "WITH"

Thiyagu_04
Starting Member

37 Posts

Posted - 2010-03-23 : 09:07:44

Thanks guys i have found the solution
eg:
with [CTE] (ID,Name,ParentID,Level,pid) as (
select ID,Name,ParentID,0,ID as pid from [Pa] c
union all
select c.ID ,c.Name,p.ParentID,(p.Level+1),p.pid from [CTE] p, [Pa] c where c.[ParentId] = p.[Id]
)
select distinct pid from [CTE] where Level=2 order by pid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources