sounds like this;With Dept_HierarchyAS(SELECT s.id as UserID,s.name AS UserName,d.name AS DeptName,d.id AS DeptID,d.parent AS ParentDeptIDFROM Users sJOIN Department dON d.id = s.department_idUNION ALLSELECT u.id,u.name,d.name,d.id,d.parentFROM Dept_Hierarchy dhJOIN Department dON d.id = dh.ParentDeptIDJOIN Users uON u.department_id = d.id)SELECT *FROM Dept_HierarchyOPTION (MAXRECURSION 0)
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs