What is self referential table in SQL?

Forums SQLWhat is self referential table in SQL?
Staff asked 7 months ago

Does anyone have an answer?

Nayan Raval replied 7 months ago

Answers (1)

Add Answer
monika gabani Marked As Accepted
Staff answered 7 months ago

“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)
AS (
SELECT ID, Name, Designation, Manager_id, cast(NULL as varchar)
FROM Employee_Information
WHERE Manager_ID IS NULL
UNION ALL
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

Subscribe

Select Categories