Avatar of HLRosenberger
HLRosenbergerFlag for United States of America asked on

help with query

I have an employee table.  Primary key of userID.   One of the columns is self referential, and gives the userID of a employee's supervisor.

I need a query that for a given userID, it lists all employees for that person, at all levels.  So for example, all records would be returned for a CEO.
Microsoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
HLRosenberger

8/22/2022 - Mon
unknown_routine

SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
    empl e
    LEFT OUTER JOIN emp m
        ON e.mgr = m.empno
Paul Jackson

It would help if you gave us the employee table structure but sounds simple enough you would just want to match the userid of the supervisor (passed in as a parameter?) against the employee table column that specifies the supervisor id for the user

Select * from employee where supervisorid = userid
ASKER
HLRosenberger

jacko72 - How does your query work?  That would only work if there was one level.   But this is a tree, a hiearchy of employees,.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
HLRosenberger

: unknown_routine -

that only seems to give me 1 level down.  UNless I goofed.
ASKER
HLRosenberger

See attached.   If I ask for the Employees for "B", I need F, G, H, I, J, K to be returned.   if I ask for "A", I get all employees.   if I ask for "D', I get L.  etc...
employees.png
ralmada

what you're looking for is a recursive query. This is done with a CTE

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

Here's an example based on your question:

;with CTE as (
	select UserID, SupervisorID, Name 0 as level from yourtable
	where SupervisorID = 0 --This is assuming CEO = 0 
	union all
	select a.UserID, a.SupervisorID, a.Name b.level +1 from yourtable a
	inner join cte b on a.supervisorID = b.userID
)
select * from cte

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
HLRosenberger

ralmada

never use CTE before, but that sound like the tickets.  I think your example might have a syntax error?   What's  "Name 0 as level"  ?

Also, b.level gives me an error.
ASKER
HLRosenberger

Ah, comma is missing prior to "0 as level".  I'm still getting syntax error on b.level
ASKER
HLRosenberger

ralmada

Ah, another command was missing.  I got it to run, no syntax error.  Looking at the results right now..

Can this is used in a  view?

 Thanks!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ralmada

yep, sorry about the commas


;with CTE as (
	select UserID, SupervisorID, Name, 0 as level from yourtable
	where SupervisorID = 0 --This is assuming CEO = 0 
	union all
	select a.UserID, a.SupervisorID, a.Name, b.level +1 from yourtable a
	inner join cte b on a.supervisorID = b.userID
)
select * from cte

Open in new window

and yes you can use it in a view:

create view yourview as
with cte as (
.....
)
select ....
ASKER
HLRosenberger

ralmada

thanks so much!  it appears to be working .  Last question.  I want the controlling user_id in the WHERE clause to be parm.   With a standard view, I could use a WHERE clause.  but here the WHERE clause is inside the CTE.  

Is there a way to do what I want?  Would I have to use a stored proc?
ASKER CERTIFIED SOLUTION
ralmada

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
HLRosenberger

ralmada

Great!  A function works.   thanks so much...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
HLRosenberger

Great!