MSSQL 2005/T-SQL Query/CTE: How to identify BACKWA

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 2005 Forums

Transact-SQL (2005)

MSSQL 2005/T-SQL Query/CTE: How to identify BACKWA

Author

Topic

dthanki
Starting Member

1 Post

Posted - 2008-09-19 : 12:30:16

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:

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

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:

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

PROBLEM

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.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-19 : 13:49:01

You need to traverse Up and Down in seperate recursions. I believe your sample output incorrectly includes rowids (6,7)


declare @t table (ROWID int, PCI varchar(3),  CI varchar(3))
insert @t
select 1, 'A1', 'A4' union all
select 2, 'A1', 'A5' union all
select 3, 'A5', 'A14' union all
select 4, 'A5', 'A15' union all
select 5, 'A6', 'A1' union all
select 6, 'A6', 'A12' union all
select 7, 'A6', 'A13' union all
select 8, 'A16', 'A6' union all
select 9, 'A1', 'A2' union all
select 10, 'A2', 'A8' union all
select 11, 'A2', 'A10' union all
select 12, 'A3', 'A9' union all
select 13, 'A10', 'A11' union all
select 14, 'A1', 'A3' union all
select 15, 'B1', 'B3' union all
select 16, 'B1', 'B4' union all
select 17, 'B2', 'B1' union all
select 18, 'B3', 'B5' union all
select 19, 'C1', 'C3' union all
select 20, 'C1', 'C4' union all
select 21, 'C2', 'C1' union all
select 22, 'C3', 'C5' 

;with cte (rowid, pci, ci, lev)
as
(
select rowid, pci, ci, 0
from   @t
where   ci = 'A1'

union all

select t.rowid, t.pci, t.ci, c.lev+1
from   @t t
join   cte c
       on c.ci = t.pci
where  c.lev >= 0

union all

select t.rowid, t.pci, t.ci, c.lev-1
from   @t t
join   cte c
       on c.pci = t.ci
where  c.lev <= 0
)
select * 
from cte
order by rowid

output:
rowid       pci  ci   lev
----------- ---- ---- -----------
1           A1   A4   1
2           A1   A5   1
3           A5   A14  2
4           A5   A15  2
5           A6   A1   0
8           A16  A6   -1
9           A1   A2   1
10          A2   A8   2
11          A2   A10  2
12          A3   A9   2
13          A10  A11  3
14          A1   A3   1

when ordered by lev, rowid:
rowid       pci  ci   lev
----------- ---- ---- -----------
8           A16  A6   -1
5           A6   A1   0
1           A1   A4   1
2           A1   A5   1
9           A1   A2   1
14          A1   A3   1
3           A5   A14  2
4           A5   A15  2
10          A2   A8   2
11          A2   A10  2
12          A3   A9   2
13          A10  A11  3

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources