If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > find all the employees under one manager (was "Need Help with Query")

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Apr 2007
Posts: 4
find all the employees under one manager (was "Need Help with Query")

I have an employee table with manager id and employee ids , i need to find all the employee ids for a manager id . Each employee can be a manager in turn . So I need to find all the employees under one manager and if any of the employee is in turn a manager , i need to find the employees under him as well .

The table structure is defined and i cannot edit it .

Please let me know if we could have a single query to do this .

Thank you
kishore
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Location: Holmestrand, Norway
Posts: 332
You don't state what version of SQL Server you are using. New to SQL Server 2005 is Common Table Expressions (CTEs). An article on MSDN describes exactly what you are looking for: Recursive Queries Using Common Table Expressions.

I'm afraid this is somewhat more tricky on SQL Server 2000.
__________________
Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Apr 2007
Posts: 4
I need to support SQL server 2000 as well as 2005 ,also other Database like oracle , Db2 and sybase. Apart from Common Table Expression , is there any way i can do it .
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Location: Holmestrand, Norway
Posts: 332
Well, the code won't be identical on the various platforms anyway. CTEs are supported on SQL Server 2005 and DB2, and perhaps the latest version of Sybase as well. It is a part of the SQL Standard since SQL:1999, so I would suggest using it where possible. For the rest you could create a temporary table, and use pretty much the same idea as in the CTE:

1. Insert all without a parent (top level)
2. Insert all whose having those in 1 as parent
3. Recursively insert all whose having their parent inserted, but are not inserted themselves yet.

I know, it's not very pretty code, but it works. Hope you got my idea
__________________
Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter
Reply With Quote
  #5 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,048
Code:
SELECT	 e.known_as_and_surname 
		AS 'Employee'
	,m.known_as_and_surname
		AS 'Manager'
FROM	        employee AS e
LEFT OUTER JOIN employee AS m
	ON m.employee_number = e.manager_number
Any good to you?
Self-join genius courtesy of Rudy at www.r937.com
__________________
George
Home | Blog
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Posts: 272
Quote:
Originally Posted by georgev
Any good to you?
That would be nice for 2 levels (manager and employee).
Triumph wants a top person's subordinates from 1, 2, x levels down.

If there is a fixed number of levels you can union that number (minus one) of select statements. If not, the temporary table that roac describes would be useful.

<edit>
Congratulations!
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Apr 2007
Posts: 4
roac approach would work i guess , but i was looking for a solution where in a single query would do the trick , but i guess its not going to be the case . I needed this solution to improve performance .
Reply With Quote
  #8 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
no single query is ever going to work the same in sql server 2000, sql server 2005, oracle, db2, and sybase

if you are writing an application that "abstracts" the database layer, you are never going to achieve your result by trying to abstract the sql

instead, you need to abstract the information request, and write specific sql modules for each database -- in this case, CONNECT BY for oracle, CTEs for those that support it (it's part of the sql standard), a recursive call for other databases, etc.
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Apr 2007
Posts: 4
Thanks to all for helping me. I think i will do the recurvise calls using Java .
Reply With Quote
  #10 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,048
Well good luck!

I'd love to see a solution to this if/when you get one
__________________
George
Home | Blog
Reply With Quote
  #11 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
Quote:
Originally Posted by triumph
Thanks to all for helping me. I think i will do the recurvise calls using Java .
Ugh.
You would get excellent performance using the algorithm suggested by ROAC. I'd bet it would beat any recursive algorithm hands down. Recursion requires a separate call for each item in the hierarchy, while ROAC's suggestion only requires one call for each level in the hierarchy.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #12 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,048
I noticed the thread changed title... This simple query solves it as the title asks
To find all the employees under ONE manager:
Code:
SELECT	 e.known_as_and_surname AS 'Employee'
FROM	        employee AS e
LEFT OUTER JOIN employee AS m
	ON m.employee_number = e.manager_number
WHERE m.known_as_and_surname = '<InsertNameOfManager>'
__________________
George
Home | Blog
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Location: 636f6d7075746572
Posts: 770
Without editing the layout of the table you're going to have difficulty getting a work around. If you know the max number of levels then you could hack it with a single query (based on rudy's example) :

Code:
SELECT 
e1.name AS 'Lvl1', 
e2.name AS 'Lvl2',
e3.name AS 'Lvl3',
e4.name AS 'Lvl4',
e5.name AS 'Lvl5',
FROM employee AS e1
LEFT OUTER JOIN employee AS e2 ON e2.employee_number = e1.manager_number
LEFT OUTER JOIN employee AS e3 ON e3.employee_number = e2.manager_number
LEFT OUTER JOIN employee AS e4 ON e4.employee_number = e3.manager_number
LEFT OUTER JOIN employee AS e5 ON e5.employee_number = e4.manager_number
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Location: 636f6d7075746572
Posts: 770
Queston : does MSSQL (2000) support recursive procedures?

Last edited by aschk; 04-05-07 at 09:56.
Reply With Quote
  #15 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
even if there is no maximum number of levels, you could still use the 4-way self-join to show just 4 levels of subtree, with links at each bottom level node if it has any further levels below it

and of course clicking on one of the links returns up 4 lower levels below that node, so in effect it "recurses" down the tree but it is the user driving the process 4 levels at a time

usually, any tree that is large enough to have "unlimited" levels is at the same time also too large to allow the complete display of the entire tree, so recursion would be neither practical nor desirable anyway

the way i see it
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On