What is self referential table in SQL?
Answers (1)Add Answer
“Recursive CTE” is a self referential table.
Self-referencing table is a table that is a parent and a dependent in the same referential constraint. I. e. 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.
WITH RECURSIVE Emp_CTE (ID, Name, Designation, Manager_id, Manager_name)
SELECT ID, Name, Designation, Manager_id, cast(NULL as varchar)
WHERE Manager_ID IS NULL
SELECT e.ID, e.Name, e.Designation, e.Manager_id, Emp_CTE.Name
FROM Employee_Information e
INNER JOIN Emp_CTE ON Emp_CTE.ID = e.Manager_id
SELECT *FROM Emp_CTE