seeking/traversing through a table

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)

seeking/traversing through a table

Author

Topic

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-04-04 : 16:46:29

I have a table that looks like this (the actual table has over 3 mln recs):

FIRST_NAME LAST_NAME RAW_ID sup_RAW_id (SUPERVISOR) sup_mgr_RAW_ID (SUPERVISOR of the MANAGER) BUS
JOHN SMITH L101185 P787997 K546899 GHH
GEORGE GREEN P021144 L566444 K756565 GHH
MICHAEL RED L323335 L322342 P343243 GHH
JAMES BLACK P212121 P925513 P247323 GHH

I am tired of maintaining a static table with folks names. I am trying to come up with SQL that would enable me to traverse through this table and come back with "DIRECT" names for each employee. DIRECT is an upper level manager. For some folks it is 3 hoerarchical steps away for some 4 etc. What I mean by DIRECT is that each employee ex. JOHN SMITH, has manager and manager's manager is someone, and that manager is manager's manager of someone (the organization is using hirerchical managerial structure, like a tree) . Knowing that teh top of my organization is managed by Z111111 (All directs report to Z111111) how I can link JOHN SMITH or JAMES BLACK to directly DIRECT who works for Z111111? Skipping all medium level managers?

Sample output would be:
FIRST_NAME LAST_NAME RAW_ID sup_RAW_id (SUPERVISOR) sup_mgr_RAW_ID (SUPERVISOR of the MANAGER) BUS DIRECT
JOHN SMITH L101185 P787997 K546899 GHH R434333
GEORGE GREEN P021144 L566444 K756565 GHH Y000002
MICHAEL RED L323335 L322342 P343243 GHH E232323
JAMES BLACK P212121 P925513 P247323 GHH W22222

note: all employees like JOHN SMITH etc must have manager and their manager must have manager and so on.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-05 : 01:10:16

you can use a recursive CTE for that

you just need employee and managerid (not even his managers) to climb up ladder and get final superior

see similar example here

http://msdn.microsoft.com/en-IN/library/ms186243(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-04-08 : 18:00:51

Thank you visakh16 - I went with your suggestion and implemented it. I just got rid off unnecessary INNER JOIN statements.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 00:17:01

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources