Query for finding managers and sub managers

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)

Query for finding managers and sub managers

Author

Topic

Mageshkumar
Starting Member

29 Posts

Posted - 2008-08-04 : 02:59:18

Hi my table is like this:

Id Name Mid
100 N1 0
101 N2 0
102 N3 100
103 N4 101
104 N5 100
105 N6 102

Now i want based on managers it have to display employees, it mean i need my dataset like this:

Managers:

Id Name MID
100 N1 0
101 N2 0

Sub Managers:

102 N3 100
104 N5 100
103 N4 101

Member:

105 N6 102

How to write the query for this?

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-04 : 03:50:26

See if this gives you what you need- http://www.eggheadcafe.com/articles/sql_server_recursion_with_clause.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 04:46:34

Also read about Trees and hierarchies in sql server help file

Madhivanan

Failing to plan is Planning to fail

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-04 : 08:45:45

You do have to pay for membership however this site has good quality video that shows you exactly how to do this and much more. Check it out, I have found it very useful.

http://www.trainingspot.com/Subscriber/Default.aspx
Video 11.1: Using Common Table Expressions (Example shows how to display hierarchies from top manager down)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-04 : 13:29:25

Have a look at recursive CTE in sql server books online

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-05 : 00:18:49

Here is the example:
http://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources