help with query

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

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

jacko72 - How does your query work?  That would only work if there was one level.   But this is a tree, a hiearchy of employees,.

: unknown_routine -

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

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

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

Select allOpen in new window

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.

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

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!

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

Select allOpen in new window

and yes you can use it in a view:

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

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?

ralmada

Great!  A function works.   thanks so much...

Great!