MSSQL 2005/T-SQL Query/CTE: How to identify BACKWARD/FORWARD Hierarchies at the same time

Question

Post reply

MSSQL 2005/T-SQL Query/CTE: How to identify BACKWARD/FORWARD Hierarchies at the same time

Darpan Thanki

Grasshopper

Points: 18
More actions
September 19, 2008 at 9:57 am

#198621

Functional Requirement
Generate a configuration item dependency chart for a specified item that shows relationship of the selected configuration item with other items in the tree. Considering the nature of our application, it is unavoidable for us to achieve this requirement outside of T-SQL i.e. we would like to resolve this at the database level only.
Data Organization/Structure
We have a relationship table that contains configuration item (CI) relationships in the following structure:
[font="Courier New"]
ROW ID PARENT CI CHILD CI
1 A1 A4
2 A1 A5
3 A5 A14
4 A5 A15
5 A6 A1
6 A6 A12
7 A6 A13
8 A16 A6
9 A1 A2
10 A2 A8
11 A2 A10
12 A3 A9
13 A10 A11
14 A1 A3
15 B1 B3
16 B1 B4
17 B2 B1
18 B3 B5
19 C1 C3
20 C1 C4
21 C2 C1
22 C3 C5[/font]
SCENARIO DETAILS
The application opens the details for a configuration item (CI) A1 and decides to view its dependencies / relationships with other items in the tree. Considering the above sample relationship data, it must be noted that the configuration item "A1" has forward relationship for e.g. A1 has two children A4 & A5, and at the same time "A1" also has backward relationships as it is a child of A6 which in turn is a parent of A12 and A13. The depth of the relationship in forward or backward direction is not known. The expected relationship dataset for A1 should be as follows:
[font="Courier New"]ROW ID PARENT CI CHILD CI
1 A1 A4
2 A1 A5
3 A5 A14
4 A5 A15
5 A6 A1
6 A6 A12
7 A6 A13
8 A16 A6
9 A1 A2
10 A2 A8
11 A2 A10
12 A3 A9
13 A10 A11
14 A1 A3[/font]
We have tried using MSSQL's Common Table Expression (CTE) to address this requirement, however could accurately identify forward relationships for A1. We are unable to accurately identify the backward relationships for the item A1. We have also tried achieving this through various other recursive approaches but haven't been successful.
Any suggestions or proposed approaches to achieve the above mentioned required would be appreciated.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004682 More actions · Answer 1

It's important... how often will the hierarchy be made to change?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)