Reports to 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

Reports to Query

Author

Topic

rc1138
Starting Member

35 Posts

Posted - 2010-11-17 : 13:25:22

Need help formulating a query that cycles through a table and picks out ID’s of users reporting under other users

Contains an ID field and 8 columns of manager ID fields (created via hierarchy)

Table Structure

ID int
MGR_LVL1 int
MGR_LVL2 int
MGR_LVL3 int
MGR_LVL4 int
MGR_LVL5 int
MGR_LVL6 int
MGR_LVL7 int
MGR_LVL8 int

e.g.

Table A data

ID 13 14 20 40
MGR_LVL1 20 20 30 50
MGR_LVL2 30 30 40
MGR_LVL3 40 40 50
MGR_LVL4 50 50 NULL
MGR_LVL5 NULL NULL NULL
MGR_LVL6 NULL NULL NULL
MGR_LVL7 NULL NULL NULL
MGR_LVL8 NULL NULL NULL

* The table is oriented vertically as it does not seem to fit if I lay it out horizontally

Say user with ID 20 logs in as per the above table we know that users 13 and 14 report directly under 20

Or if 40 logs in 30,20,13 and 14 should show up.

Thanks again!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-17 : 13:41:00

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-18 : 07:07:16

what kind of design is this? why do you need so much fields for each level? what if level goes beyond 8? Actually you need only 2 fields ID and Mgr_ID.
see below for example
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