Wiki Home

Self - Joins


Namespace: Wiki
This is a query where a table is joined to itself. As an example, the Employee table supplied as a sample with VFP includes details of both staff and managers. Every entry in the table includes a reports_to field which is a foreign key to the employee id of that person's manager. You can get a list of staff and their managers if you use a self join to join the employee table back to itself like this:

SELECT e.last_name as Staff, s.last_name as Supervisor ;
FROM employee e inner JOIN employee s ;
ON e.reports_to = s.emp_id


Geoff Franklin

The above code will not list employees who have not been assigned any supervisor. The following code will also include such employees:

SELECT e.last_name as Staff, NVL(s.last_name,'') as Supervisor ;
FROM employee e LEFT outer JOIN employee s ;
ON e.reports_to = s.emp_id


To list ONLY those employees who have no supervisor, add condition -- Where isNULL(s.last_name)


- Ravi
Category Data
( Topic last updated: 2006.05.28 08:06:38 PM )