How do I Self Join a Recursive Query ?

[Guest_imported]

Hi !

I have an EMPLOYEE table as below.

Table - EMPLOYEE
Id (Int) (P)
EmployeeName Varchar(30)
Parent_Id (Int) (Reference to EMPLOYEE.Id)

I want to retrieve all the Employees of any given Manager.
For eg: Given a CEO, I want to retrieve all the Managers and Sub Managers and Sub Sub Managers ... under it.

This is a typical example of a Self join Table. How can I do that.

Can anyone help ??

Regards.

 

[RickSpr]

In SQL:
SELECT EMPLOYEE.Id, EMPLOYEE.EmployeeName, MANAGER.EmployeeName FROM (EMPLOYEE INNER JOIN EMPLOYEE AS MANAGER ON EMPLOYEE.Parent_Id = MANAGER.Id)

In a query grid, add the EMPLOYEE table twice. The second one will be named EMPLOYEE_1. Drag the Parent_Id field from EMPLOYEE to the Id field of EMPLOYEE_1. Any fields you put in the grid from EMPLOYEE will be data about the employee. Any fields from EMPLOYEE_1 will be the manager's data.

Caution: If any employees have a null value in Parent_Id, you need to make this an outer join, or those employees won't appear in the query results. To make a relationship an outer join, double click on the join line and select option 2 (Include ALL records from 'EMPLOYEE' and only those from 'EMPLOYEE_1' where the joined fields are equal). Rick Sprague