How to work with self-referencing tables

Last modified: April 26, 2022

The guide describes how to work with self-referencing tables in Master-Detail Browser.

A self-referencing table is a table that is parent and dependent in the same referential constraint, that is in such tables a foreign key constraint can reference columns within the same table.

The example specified below shows how to view data in a self-referencing table regarding available relations between columns using Master-Detail Browser.

Prerequisites

To perform the steps described in the Viewing data in a self-referencing table part of this topic, you will need a self-referencing table itself. Below is an example of such table (the Employees table) and it will be used in the Viewing Data in a Self-Referencing Table section as a sample table:

EmpNo	EName	Job	MGR	HireDate	Sal	Commission	DeptNo
7369	SMITH	CLERK	7902	2/17/1980 12:00:00 AM	800	(null)	20
7499	ALLEN	SALESMAN	7698	02/20/1981 12:00:00 AM	1600	300	30
7521	WARD	SALESMAN	7698	02/22/1981 12:00:00 AM	1250	500	30
7566	JONES	MANAGER	7839	04/02/1981 12:00:00 AM	2975	(null)	20
7654	MARTIN	SALESMAN	7698	09/28/1981 12:00:00 AM	1250	1400	30
7698	BLAKE	MANAGER	7839	05/01/1981 12:00:00 AM	2850	(null)	30
7782	CLARK	MANAGER	7839	06/09/1981 12:00:00 AM	2450	(null)	10
7788	SCOTT	ANALYST	7566	07/13/1980 12:00:00 AM	3000	(null)	20
7839	KING	PRESIDENT	(null)	11/17/1980 12:00:00 AM	5000	(null)	10
7844	TURNER	SALESMAN	7698	09/08/1981 12:00:00 AM	1500	0	30
7876	ADAMS	CLERK	7788	07/13/1987 12:00:00 AM	1100	(null)	20
7900	JAMES	CLERK	7698	12/03/1980 12:00:00 AM	950	(null)	30
7902	FORD	ANALYST	7566	12/03/1980 12:00:00 AM	3000	(null)	20
7934	MILLER	CLERK	7782	01/23/1982 12:00:00 AM	1300	(null)	10

Viewing data in a self-referencing table

Task: It is required to view all employees regarding the levels of company hierarchy using the data available in the sample Employees table.

Solution: Because the manager is also an employee, there is a relationship between the MGR and EmpNo columns. The EmpNo column contains the identification number of all employees, and the MGR column contains the identification number of the manager to whom a person subordinates. There is a null value for the company president in the MGR column, as the president doesn’t subordinate to anyone.

To view data in a self-referencing table

1. On the Database menu, click Master-Detail Browser.

2. In the Master-Detail Browser document that opens, drag the Employees table you’ve created to the designer surface.

3. Create a relationship between the MGR and EmpNo columns by selecting the MGR column and dragging it to the EmpNo column. An arrow indicating the relationship will be displayed on the diagram.

Note

There is no foreign key in this table. However, if the relation between columns was established earlier, the relationship will be automatically displayed on the diagram.

4. On the document toolbar, click Browse Data.

5. In the Data view that opens, select the row with PRESIDENT specified in the Job column in the upper part of the view and click + next to each detail record in the lower part of the view to see the next level of the company hierarchy and the employees list: