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.
LVL 1
HLRosenbergerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

unknown_routineCommented:
SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
    empl e
    LEFT OUTER JOIN emp m
        ON e.mgr = m.empno
0
Paul JacksonSoftware EngineerCommented:
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
0
HLRosenbergerAuthor Commented:
jacko72 - How does your query work?  That would only work if there was one level.   But this is a tree, a hiearchy of employees,.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

HLRosenbergerAuthor Commented:
: unknown_routine -

that only seems to give me 1 level down.  UNless I goofed.
0
HLRosenbergerAuthor Commented:
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
0
ralmadaCommented:
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

0
HLRosenbergerAuthor Commented:
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.
0
HLRosenbergerAuthor Commented:
Ah, comma is missing prior to "0 as level".  I'm still getting syntax error on b.level
0
HLRosenbergerAuthor Commented:
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!
0
ralmadaCommented:
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 ....
0
HLRosenbergerAuthor Commented:
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?
0
ralmadaCommented:
oh, in that case you can't use the view. You will have to do it with a function or a stored procedure

create function dbo.yourfunction(@yourpar int)
returns table
as
return
with CTE as (
      select UserID, SupervisorID, Name, 0 as level from yourtable
      where SupervisorID = @yourpar
      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


and then call your function like

select * from dbo.yourfunction(0)  -- 0 = CEO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HLRosenbergerAuthor Commented:
ralmada

Great!  A function works.   thanks so much...
0
HLRosenbergerAuthor Commented:
Great!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.