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

General SQL Server Forums

New to SQL Server Programming

Hierarchy query

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-01-05 : 04:56:50

Dear Gurus,

I have a VIEW like below:

How do I query with given one EmpID to:
1. Get all the above BossID?
Example: Given EmpID = 12

will return EmpID IN (01,03,09)?

EmpID	Name		BossID	Lineage			Depth
01	Boss		NULL	/			0
02	Manager1	01	/02/			1
03	Manager2	01	/03/			1
04	Staff1		03	/03/04/			2
05	Staff2		03	/03/05/			2
06	Staff3		03	/03/06/			2
07	Satff4		03	/03/07/			2
08	Staff5		03	/03/08/			2
09	Staff6		03	/03/09/			2
10	Staff7		09	/03/09/10		3
11	Staff8		09	/03/09/11		3
12	Staff9		09	/03/09/12		3

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-05 : 05:10:17

If you are on SQL Server 2000 then read this: http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

On a higher version search for: "recursive CTE example" and I'm sure you will find a solution.

No, you're never too old to Yak'n'Roll if you're too young to die.

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-01-05 : 05:51:08

Yes, this is the article that what my View is made and based on

But the article didn't answer my question... at least to my understanding...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-05 : 09:44:51

its as simple as


;With CTE
AS
(
SELECT EmpID,	
Name,		
BossID,
CAST(1 AS int) AS [level]
FROM Table
WHERE EmpID = 12

UNION ALL

SELECT t.EmpID,	
t.Name,
t.BossID,
c.[Level] + 1
FROM CTE c
INNER JOIN Table t
ON t.EmpID = c.BossID
)

SELECT *
FROM CTE
WHERE [Level] >1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-01-05 : 20:26:50

Amazing.. I tried and it works! But the thing is, I still having hard time trying to digest how it works.... LOL...
Anyway, Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-06 : 13:40:37

quote:
Originally posted by calvinfoo

Amazing.. I tried and it works! But the thing is, I still having hard time trying to digest how it works.... LOL...
Anyway, Thanks!

see explanation here

http://msdn.microsoft.com/en-us/library/ms186243.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources