Nested structure.

[ReportingAnalyst]

Hi,

I have 2 seperate queries from a nested table structure.

This query returns the emp and his subordinates.

SELECT     s2.*, dbo.Employee.FirstName + ' ' + dbo.Employee.LastName AS Emp_Name
FROM         dbo.Stack1 s1 INNER JOIN
                      dbo.Employee ON s1.employee = dbo.Employee.UniqueIdentifier CROSS JOIN
                      dbo.Stack1 s2
WHERE     (s1.Lft BETWEEN s2.Lft AND s2.Rgt) AND (s2.employee = '1472')

This query returns the indentation of the node in the org structure.

SELECT     TOP 100 PERCENT COUNT(s2.employee) AS Indentation, s1.employee, dbo.Employee.FirstName + ' ' + dbo.Employee.LastName AS Emp_Name, 
                      dbo.Employee.EmpID
FROM         dbo.Stack1 s1 INNER JOIN
                      dbo.Employee ON s1.employee = dbo.Employee.UniqueIdentifier CROSS JOIN
                      dbo.Stack1 s2
WHERE     (s1.Lft BETWEEN s2.Lft AND s2.Rgt)
GROUP BY s1.Lft, s1.employee, dbo.Employee.FirstName + ' ' + dbo.Employee.LastName, dbo.Employee.EmpID
ORDER BY s1.Lft

My question is:

How can I combine the above two in order to get the indentation for the employee and his subordinates?

For instance, I select an employee who is in level 2, but he has employees with indentation, 3, 4, and 5. Can I combine the two queries above?

Thanks.