Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a typical self-referencing employee table. The data is hierarchical and I use the following UDF to encapsulate a simple CTE to derive an output of a particular person's direct and indirect reports. That is to say I can pass in a single employee and get back a listing of them and everyone under them.

I want to create a similar function that would allow me to take a boss and employee param and detect if the employee reports into the boss (or the boss param is indeed the boss - direct or indirect of the employee)

whats the best way to accomplish this logic using the DB structure outlined below as what I have today?

CREATE TABLE [dbo].[Employees](
    [EmployeeId] [int] NOT NULL,
    [managerId] [int] NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [FullName] [nvarchar](100) NOT NULL,
    [CorpEmailAddress] [nvarchar](510) NULL
) ON [PRIMARY]


ALTER FUNCTION [dbo].[fnGetEmployeeHierarchy]   
(    
  @EmployeeId int = null  
)  
RETURNS TABLE   
AS  
RETURN   
(  
  WITH yourcte AS  
  (  
    SELECT EmployeeId, ManagerID, AMRSNTID, FullName--, Name  
    FROM Employees  
    WHERE EmployeeId = isnull(@EmployeeId,EmployeeId)  
    UNION ALL  
    SELECT e.EmployeeId, e.ManagerID, e.AMRSNTID, e.FullName--, e.Name  
    FROM Employees e  
    JOIN yourcte y ON e.ManagerID = y.EmployeeId  
  )  
SELECT EmployeeId, ManagerID, AMRSNTID, FullName--, Name  
FROM yourcte  
)
share|improve this question
    
You may want to take a look at Joe Celko's Trees and Hierarchies in SQL for Smarties: amzn.com/1558609202 –  Eric Humphrey - lotsahelp Feb 8 '11 at 14:22
    
I think you're relying too much on the CTE and not enough on the actual query. What do you think is going wrong here? What sort of data are you getting back? –  jcolebrand Feb 8 '11 at 14:50
    
this is fine but i am brain storming wheather or not to use the cte solution in part to determine if a empl1 reports to empl2, such that if i return a cte table of everyone that reports to empl1 i can scan for empl2 in that result... if found then they do report to them else false would be the result. do you have a sql query in mind that can reproduce this without the use of a CTE? i dont see how... –  kacalapy Feb 8 '11 at 15:27
    
You don't need a CTE unless you're trying to do something much more complex here. Also, starting a comment with @jcolebrand would notify me that you posted. ~~~ All you need is a left join using the clause that comes after your UNION. This is not that hard of a problem, overall. (caveat: if all you're doing is the "Who's my manager?" basic query problem, it's not that hard, if I'm misunderstanding the requirement, then ... probably hard) –  jcolebrand Feb 8 '11 at 15:41
    
I would agree with drachenstern. If you are simply attempting to discover if employee X reports to manager Y, provided X and Y, this is a simple SELECT FROM Employees WHERE EmployeeID = X AND ManagerID = Y. A record returned indicates Yes. An empty resultset indicates No. –  Matt M Feb 8 '11 at 16:02
show 3 more comments

2 Answers

up vote 2 down vote accepted

If you just want to return a true or false upon comparing an employee and a manager, try this:

CREATE FUNCTION [dbo].[fnGetEmployeeHierarchy]   
(    
    @EmployeeId INT,
    @ManagerId INT
)
RETURNS BIT   
AS
BEGIN
    DECLARE @return BIT;
    SET @return = 0;


    WITH yourcte AS  
    (  
        SELECT 
            EmployeeId, 
            ManagerID, 
            FullName
        FROM Employees  
        WHERE EmployeeId = @EmployeeId

        UNION ALL  
        SELECT 
            e.EmployeeId, 
            e.ManagerID, 
            e.FullName
        FROM Employees e  

            INNER JOIN yourcte y 
                ON e.EmployeeId = y.ManagerId
    )  

    SELECT 
        @return = 1
    FROM yourcte
    WHERE EmployeeId = @ManagerId;

    RETURN @return;
END
share|improve this answer
    
Just to ask the question: did you source that query from somewhere else? –  jcolebrand Feb 10 '11 at 17:14
    
@jcolebrand I made alterations to the CTE posted by kacalapy in this question. Other than that, it is my code. Any resemblance is coincidental. –  Matt M Feb 10 '11 at 20:38
    
cool, just being nosey ;) –  jcolebrand Feb 10 '11 at 21:03
    
@jcolebrand No problem. I have been dealing with recursive CTEs a lot over the last few weeks, so this stuff is pretty fresh in my head. I didn't get a chance to test this particular function, so it is highly possible it has some error, but it should be a start. –  Matt M Feb 11 '11 at 13:30
add comment

Since to me it's already a well known and solved problem, and since the commentary on here seems to agree with me:

http://stackoverflow.com/questions/3859882/sql-recursive-function-to-find-managers

Are you wanting to return the results for each lowest employee in the set, and that's what's making it hard?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.