A join does not necessarily involve two different tables; you can join a table to itself, thus creating a self-referencing join. A self-referencing join is a join from one column of a table to another column of the same table. Joining a table to itself can be useful when you want to compare values in a column to other values in a different column in the same table.
An example of when such a join is required is when you want to report on the hierarchical structure of an organization using a Personnel database. Typically, all employee records are held in a single table, regardless of employee status. Therefore, a self-referencing join is required to report on the hierarchical relationship between those employees.
This self-referencing join is effectively a recursive loop; the path forms a closed circuit. However, you cannot resolve this loop by using the usual method of detecting the cardinalities and then detecting aliases. The cardinality detection tool cannot work on a self-referencing join. Moreover, a structure expressed this way does not infer the correct SQL.
The Motors database contains an EMPLOYEE table in which each employee is uniquely identified by the Emp_Id field, and each employee has a manager who is identified by the Emp_Mgr_Id field. The managers are themselves employees; therefore, the table contains a hierarchical structure. If you want to add a join to link each employee with their respective manager, the obvious way is to link the Emp_Mgr_Id field to the Emp_Id field. You can therefore use it to look up the Emp_Id codes in the Employee table and identify the manager’s name.
To resolve a recursive loop, simply alias the table and join the original table to the alias as you would if you had two different tables on your data foundation.