Hierarchy Query - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Hierarchy Query

Author

Topic

Pavans
Starting Member

4 Posts

Posted - 2010-06-24 : 04:19:01

Hi All,

A
| |
| |
C B
|
|
D

A Is parent to B
A is parent to C
C is Parent to D
A is parent to D -- The first four records in the below example shows it.
also, A is child to itself
B is child to itself
C is child to itself
D is child to itself. The last four records in the below examples expalins that.

For top_flag and bottom_flag the logic is

for the 1st row :
A is the parent at the top level so Top_flag is 'Y' and B doesnt have a child so Bottom_Flag is 'Y'

for the 2nd row:

A is the parent at the top level so Top_flag is 'Y' and C has a child so Bottom_Flag is 'N'

for the 3rd row:

C is the parent and not at the top level so Top_flag is 'N' and D has no child so Bottom_Flag is 'Y'

ChildLevel and ParentLevel are indicates what level the child and parent are.

Result should be :

Parent,Child,Top_flag,Bottom_flag,ChildLevel,ParentLevel
A,B,Y,Y,2,1
A,C,Y,N,2,1
C,D,N,Y,3,2
A,D,Y,Y,3,1
A,A,Y,N,1,1
B,B,N,Y,2,2
C,C,N,N,2,2
D,D,N,Y,3,3

I have used the below query,

WITH temp_A (s_id, id_over, id_under, TYPE_ID, t_tp, iteration ) AS
(
SELECT surr_id, t_id_over, t_id_under, TYPE_ID,t_tp,1
FROM
A
WHERE
TYPE_ID in ('L', 'B')
UNION ALL SELECT b.s_id, a.id_over, b.ID_UNDER, a.TYPE_ID,b.t_tp, a.iteration + 1
FROM temp_A AS a, A AS b
WHERE a.id_under = b.ID_OVER
and b.TYPE_ID in ('L', 'B')
and b.t_tp <> 'D'
)
SELECT s_id, id_under, id_over, TYPE_ID,isrt_tp, iteration
FROM temp_orgchart

Result I am getting is:

Parent,Child,Iteration
A,B,1
A,C,1
C,D,1
A,D,2

Any help is really appreciated.

Thanks in Advance

Pavan

Pavan

Pavans
Starting Member

4 Posts

Posted - 2010-07-13 : 04:50:31

Guys...any help.

Pavan

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-07-13 : 11:53:39

What is the table schema?

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources