Solved: CONNECT BY | Experts Exchange

you can try:
1.
http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

2.
Self join

> 1. http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

I imagine that the poster wants a resultset to get the job done. Using this proc you would need to create another proc to create a global temp table and to call it initially. Then change the print statements to INSERTs to the temp table. Once the inner proc has finished running SELECT * from the global temp table.

> 2. Self join

Self join only works if you know in advance the depth of the tree.

I couldn't find the proc I had written, but here is a test script you could turn into something more useful.

CREATE TABLE #Emp (EmpID int, JobTitle VarChar(100) NOT NULL, Boss Int NULL)

INSERT #Emp SELECT 1, 'President', NULL
INSERT #Emp SELECT 2, 'Vice President', 1
INSERT #Emp SELECT 3, 'CEO', 2
INSERT #Emp SELECT 4, 'CTO', 2
INSERT #Emp SELECT 5, 'Group Project Manager', 4
INSERT #Emp SELECT 6, 'Project Manager 1', 5
INSERT #Emp SELECT 7, 'Project Manager 2', 5
INSERT #Emp SELECT 8, 'Team Leader 1', 6
INSERT #Emp SELECT 9, 'Software Engineer 1', 8
INSERT #Emp SELECT 10, 'Software Engineer 2', 8
INSERT #Emp SELECT 11, 'Test Lead 1', 6
INSERT #Emp SELECT 12, 'Tester 1', 11
INSERT #Emp SELECT 13, 'Tester 2', 11
INSERT #Emp SELECT 14, 'Team Leader 2', 7
INSERT #Emp SELECT 15, 'Software Engineer 3', 14
INSERT #Emp SELECT 16, 'Software Engineer 4', 14
INSERT #Emp SELECT 17, 'Test Lead 2', 7
INSERT #Emp SELECT 18, 'Tester 3', 17
INSERT #Emp SELECT 19, 'Tester 4', 17
INSERT #Emp SELECT 20, 'Tester 5', 17

-- proc would begin here
SET NOCOUNT ON

DECLARE @EmpID1 int, @EmpID2 int
DECLARE @BossPosition VarChar(100)

CREATE TABLE #Temp (EmpID int, JobTitle VarChar(100), Boss Int, Bosses VarChar(2000))

-- dump to temp table
INSERT INTO #Temp
(EmpID, JobTitle, Boss)
SELECT EmpID, JobTitle, Boss
FROM #Emp

-- get the first
SELECT @EmpID1 = MIN(EmpID)
FROM #Temp

WHILE @EmpID1 IS NOT NULL
BEGIN

SELECT @EmpID2 = MIN(T2.EmpID)
FROM #Temp T1
INNER JOIN #Temp T2 ON
T1.Boss = T2.EmpID
WHERE T1.EmpID = @EmpID1

WHILE @EmpID2 IS NOT NULL
BEGIN

SELECT @BossPosition = JobTitle FROM #Temp WHERE EmpID = @EmpID2

UPDATE #Temp
SET Bosses =
CASE
WHEN Bosses IS NULL THEN @BossPosition
ELSE Bosses + ' > ' + @BossPosition
END
WHERE EmpID = @EmpID1

SELECT @EmpID2 = MIN(T2.EmpID)
FROM #Temp T1
INNER JOIN #Temp T2 ON
T1.Boss = T2.EmpID
WHERE T2.EmpID > @EmpID2

END

SELECT @EmpID1 = MIN(EmpID)
FROM #Temp
WHERE EmpID > @EmpID1

END

SELECT * FROM #Temp

DROP TABLE #Temp
-- proc would end here

DROP TABLE #Emp

ispaleny

I think to make hierarchies usable, you must have them in double forms.
One in normalized architecture(IdNode,ParentNode,NodeName,...),
other in denormalized one(IdNode,ChildNode,ChildLevel).
The denormalized table is filled by trigger, it can be indexed and queries are very simple.

A_B_C
| |_D
|
|_E_F

drop table #norm,#denorm
GO
select IdNode='x',ParentNode='x',NodeName=convert(varchar(50),'') INTO #norm where 0=1
UNION ALL SELECT 'A','A','president'
UNION ALL SELECT 'B','A','vice1'
UNION ALL SELECT 'C','B','sec1'
UNION ALL SELECT 'D','B','sec2'
UNION ALL SELECT 'E','A','vice1'
UNION ALL SELECT 'F','E','sec3'

select IdNode='x',ChildNode='x',ChildLevel=convert(int,0) INTO #denorm where 0=1
UNION ALL SELECT 'A', 'A', 0
UNION ALL SELECT 'A', 'B', 1
UNION ALL SELECT 'A', 'C', 2
UNION ALL SELECT 'A', 'D', 2
UNION ALL SELECT 'A', 'E', 1
UNION ALL SELECT 'A', 'F', 2
UNION ALL SELECT 'B', 'A', -1
UNION ALL SELECT 'B', 'B', 0
UNION ALL SELECT 'B', 'C', 1
UNION ALL SELECT 'B', 'D', 1
UNION ALL SELECT 'C', 'A', -2
UNION ALL SELECT 'C', 'B', -1
UNION ALL SELECT 'C', 'C', 0
UNION ALL SELECT 'D', 'A', -2
UNION ALL SELECT 'D', 'B', -1
UNION ALL SELECT 'D', 'D', 0
UNION ALL SELECT 'E', 'A', -1
UNION ALL SELECT 'E', 'E', 0
UNION ALL SELECT 'E', 'F', 1
UNION ALL SELECT 'F', 'A', -2
UNION ALL SELECT 'F', 'E', -1
UNION ALL SELECT 'F', 'F', 0

--Single node
select IdNode,ParentNode,NodeName
from #norm
where NodeName='sec1'

--Single branch
select n.NodeName,n2.NodeName,dn.ChildLevel
from #norm n
join #denorm dn on n.IdNode=dn.IdNode
join #norm n2 on dn.ChildNode=n2.IdNode
where n.NodeName='sec1'

--All ways
select n.NodeName,n2.NodeName,n3.NodeName,dn.ChildLevel,dn2.ChildLevel
from #norm n
join #denorm dn on n.IdNode=dn.IdNode
join #norm n2 on dn.ChildNode=n2.IdNode
join #denorm dn2 on dn.ChildNode=dn2.IdNode
join #norm n3 on dn2.ChildNode=n3.IdNode

--All shortest ways
select n.NodeName,n2.NodeName,n3.NodeName
from #norm n
join #denorm dn on n.IdNode=dn.IdNode
join #norm n2 on dn.ChildNode=n2.IdNode
join #denorm dn2 on dn.ChildNode=dn2.IdNode
join #norm n3 on dn2.ChildNode=n3.IdNode
join
(
select x.FromNode,x.ToNode,x.Steps,Steps1=min(ABS(dn.ChildLevel))
from #denorm dn
join #denorm dn2 on dn.ChildNode=dn2.IdNode
join
(
select FromNode=dn.IdNode,ToNode=dn2.ChildNode,Steps=Min(ABS(dn.ChildLevel)+ABS(dn2.ChildLevel))
from #denorm dn
join #denorm dn2 on dn.ChildNode=dn2.IdNode
group by dn.IdNode,dn2.ChildNode
) x on x.FromNode=dn.IdNode and x.ToNode=dn2.ChildNode and (ABS(dn.ChildLevel)+ABS(dn2.ChildLevel))=x.Steps
group by x.FromNode,x.ToNode,x.Steps
) xx on xx.FromNode=dn.IdNode and xx.ToNode=dn2.ChildNode and (ABS(dn.ChildLevel)+ABS(dn2.ChildLevel))=xx.Steps and ABS(dn.ChildLevel)=xx.Steps1

David_Cameron

I'd agree that this is a good case for denormalisation. It would get fairly messy though, lots of work with strings.

EugeneZ

ORACLE example:

The following hierarchical query uses the CONNECT BY clause to define the relationship between employees and managers:

SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME MANAGER_ID
----------- ------------------------- ----------
101 Kochhar 100
108 Greenberg 101
109 Faviet 108
110 Chen 108
111 Sciarra 108
112 Urman 108
113 Popp 108
200 Whalen 101

-------------------------------------------------------
--------------------------------------------------------
-------------------------------------------------------

---SQL Server example:

use Northwind
GO

select emp.EmployeeID EMPLOYEE_ID,
emp.LastName [Employee LastName],
mgr.EmployeeID [Manager_ID]
from Employees emp left join Employees mgr
on emp.reportsto=mgr.EmployeeID

ispaleny

David_Cameron,
I used strings to highlight difference between NodeId and it's Level only.
You probably mean the very nice semi-denormalized solution with paths.

select IdNode='x',ParentNode='x',NodeName=convert(varchar(50),''),Path=convert(varchar(200),'')
INTO #semidenorm where 0=1
UNION ALL SELECT 'A','A','president','/A/'
UNION ALL SELECT 'B','A','vice1' ,'/A/B/'
UNION ALL SELECT 'C','B','sec1' ,'/A/B/C/'
UNION ALL SELECT 'D','B','sec2' ,'/A/B/D/'
UNION ALL SELECT 'E','A','vice1' ,'/A/E/'
UNION ALL SELECT 'F','E','sec3' ,'/A/E/F/'

--Single branch
select n2.NodeName,ChildLevel=(len(n2.Path)-len(replace(n2.Path,'/','')))-(len(n1.Path)-len(replace(n1.Path,'/','')))
from #semidenorm n1
join #semidenorm n2 on (n2.Path like '%/'+n1.IdNode+'/%') or (n1.Path like '%/'+n2.IdNode+'/%')
where n1.IdNode='B'

CleanupPing

MH0:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.

monosodiumg

No comment has been added to this question in more than 257 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
Accept: David_Cameron http:#8106538

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer