Mark,

I am having a problem here.. I looked at Matt's query and it works almost the same as mine. You state that you want one row, while your sample data would allow for that if you added a 5th employee that was a supervisor reporting to manager and a 6th employee reporting to the 5th employee, you would get 2 rows. My query gives it to you on one row, Matt's query gives you it on two. Let me state the full problem as I see it.

1. You have a table that includes all employees, whether they are managers, supervisors, or just staff.

2. You have a link table that defines the relationships between the employees in the first table, this link table is able to support a three level hierarchy (and only three levels), Manager at the top, supervisor in the middle, and staff at the bottom.

3. For a given ManagerId in the link table you want to show the supervisor employee and the staff member, you want both items in the same row, and you only want a single row.

Now, problems I see.

1. Item 1, looks great, very common implementation..

2. Item 2, usually implemented differently since this implementation limits you to a three level hierarchy, it is often implemented as Id and Parent Id, so you have to traverse the list to get all the levels, but it can support many levels.

3. Item 3, presents a problem because with only slightly different sample data, the addition of a supervisor and a staff member reporting to manager one guarantees a second row without additional where clause items.

Is my analysis of the table layout or the desired output incorrect?

If so then the query I presented is the solution.

If not explain which item is incorrect and give an/another example if possible.

CEWII