Writing a Recursive SQL Query on a Self-Referencing Table

Writing a recursive SQL query on a self-referencing table

well here's working solution

SQL FIDDLE EXAMPLE

declare @id uniqueidentifier
set @id = '33333333-3333-3333-3333-333333333333'

;with ItemPath as 
(
    select a.[Id], a.[Name], a.ParentID 
    from Items a
    where Id = @id

    union all

    select parent.[Id], parent.[Name] + '/' + a.[Name], parent.ParentID 
    from ItemPath as a
        inner join Items as parent on parent.id = a.parentID
)
select * 
from ItemPath
where ID = '11111111-1111-1111-1111-111111111112'

I don't like it much, I think better solution will be to do it other way around. Wait a minute and I try to write another query :)

UPDATE here it is

SQL FIDDLE EXAMPLE

create view vw_Names
as
    with ItemPath as 
    (
        select a.[Id], cast(a.[Name] as nvarchar(max)) as Name, a.ParentID 
        from Items a
        where Id = '11111111-1111-1111-1111-111111111112'

        union all

        select a.[Id], parent.[Name] + '/' + a.[Name], a.ParentID 
        from Items as a
            inner join ItemPath as parent on parent.id = a.parentID
    )
select * 
from ItemPath

and now you can use this view

declare @id uniqueidentifier
set @id = '33333333-3333-3333-3333-333333333333'

select * 
from vw_Names where Id = @id

How to write a recursive query for a self referencing table and junction table (duh)

Assume you have a table sources with a column id.
The query to get the destinations for these particular sources would be as follows:

SELECT id_destination
FROM relation_entite
JOIN sources ON relation_entite.id_source = sources.id;

Then just plug this into a CTE, to get the basic recursive CTE for searching a subtree:

WITH RECURSIVE ids(id) AS (
  VALUES('m.06w4why')                -- start with this record
  UNION ALL
  SELECT id_destination              -- get destinations for any previous sources
  FROM relation_entite
  JOIN ids ON relation_entite.id_source = ids.id
)
SELECT id FROM ids;

(If it is possible that there are loops in the data, you must use UNION instead of UNION ALL.)

Recursive self referenced table to flat structure

Using CTE:

DECLARE @t TABLE (Id CHAR(1), TypeAId CHAR(1), TypeBId CHAR(1))

INSERT INTO @t VALUES
    ('A', NULL, NULL),
    ('B', NULL, NULL),
    ('C', 'A', NULL),
    ('D', 'B', 'C'),
    ('E', NULL, 'C')

-- All entities flattened
;WITH l1 AS (
    SELECT t.TypeAId AS Parent, t.Id AS Child
    FROM @t t
    WHERE t.TypeAId IS NOT NULL
    UNION
    SELECT t.TypeBId AS Parent, t.Id AS Child
    FROM @t t
    WHERE t.TypeBId IS NOT NULL)

-- Join l1 with itself
,l2 AS (
    SELECT l1.Parent, l2.Child
    FROM l1 l1
    INNER JOIN l1 l2 ON l2.Parent = l1.Child)

SELECT * FROM l1
UNION ALL SELECT * FROM l2
ORDER BY Parent

SQL recursion on a self-referencing table to obtain specific order

You would need a recursive CTE to build the hierarchy and maintain the sequence

Example

with cte1 as (
      Select [Id]
            ,[LinkSlug]
            ,[ParentPageId]
            ,[Order]
            ,Seq  = cast(10000+Row_Number() over (Order by [Order]) as varchar(500))
      From   pages
      Where  [ParentPageId] is null
      Union  All
      Select cte2.[Id]
            ,cte2.[LinkSlug]
            ,cte2.[ParentPageId]
            ,cte2.[Order]
            ,Seq  = cast(concat(cte1.Seq,'.',10000+Row_Number() over (Order by cte2.[Order])) as varchar(500))
      From   pages cte2 
      Join   cte1 on cte2.[ParentPageId] = cte1.[Id])
Select *
 From cte1
 Order By Seq

Results

Self Referencing Table SQL query

Ref: SQLite WITH clause

You need a "Recursive CTE" (common table expression) to traverse the organization hierarchy. Like this:

Query

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

Result:

| ID |  Name   | Designation | Manager_id | Manager_name |
|----|---------|-------------|------------|--------------|
|  1 | Raja    | CEO         | null       | null         |
|  3 | Kavi    | COO         | 1          | Raja         |
|  2 | Mani    | CTO         | 1          | Raja         |
|  4 | Murugan | Head        | 3          | Kavi         |
|  5 | Alpha   | Head(Fin)   | 4          | Murugan      |
|  7 | Kannan  | Head        | 4          | Murugan      |

Setup:

CREATE TABLE "Employee_Information" ("id" INTEGER PRIMARY KEY AUTOINCREMENT 
NOT NULL, "name" varchar, "designation" varchar, "manager_id" integer references employee_information(id));

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (1, 'Raja', 'CEO', NULL)
;

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (2, 'Mani', 'CTO', '1')
;

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (3, 'Kavi', 'COO', '1')
;

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (4, 'Murugan', 'Head', '3')
;

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (5, 'Alpha', 'Head(Fin)', '4')
;

INSERT INTO Employee_Information
    ("ID", "Name", "Designation", "Manager_id")
VALUES
    (7, 'Kannan', 'Head', '4')
;

Demo

Query 2

WITH RECURSIVE Emp_CTE (ID, Name, Designation, Manager_id, Manager_name, namepath)
AS (
    SELECT ID, Name, Designation, Manager_id, cast(NULL as varchar), name as namepath
    FROM Employee_Information
    WHERE Manager_ID IS NULL
    UNION ALL
        SELECT e.ID, e.Name, e.Designation, e.Manager_id, Emp_CTE.Name
  , Emp_CTE.namepath || '/' || e.Name 
        FROM Employee_Information e
        INNER JOIN Emp_CTE ON Emp_CTE.ID = e.Manager_id
    )
SELECT *
FROM Emp_CTE

Result:

| ID |  Name   | Designation | Manager_id | Manager_name |         namepath         |
|----|---------|-------------|------------|--------------|--------------------------|
|  1 | Raja    | CEO         | null       | null         | Raja                     |
|  3 | Kavi    | COO         | 1          | Raja         | Raja/Kavi                |
|  2 | Mani    | CTO         | 1          | Raja         | Raja/Mani                |
|  4 | Murugan | Head        | 3          | Kavi         | Raja/Kavi/Murugan        |
|  5 | Alpha   | Head(Fin)   | 4          | Murugan      | Raja/Kavi/Murugan/Alpha  |
|  7 | Kannan  | Head        | 4          | Murugan      | Raja/Kavi/Murugan/Kannan |

Get path from self referencing table

First, you are trying to filter values ​​in the wrong place. This should be done not in the CTE, but in the main query, since the CTE is used to build the tree and generate the Name value.

Second, you have an error in the CTE in the recursive part when joining the CTE. You have on parent.ParentId = a.Id but you need the opposite on a.ParentId = parent.Id.

So you query would be like this

declare @id int;
set @id = 4;

WITH folder_tree AS (
    SELECT 
        id, 
        CAST(name AS nvarchar(max)) AS name, 
        parentid 
    FROM folders 
    WHERE parentid IS NULL
    UNION ALL
    SELECT 
        f.id,
        CAST(ft.name + '/' + f.name AS nvarchar(max)),
        f.parentid 
    FROM folders f
    JOIN folder_tree ft ON f.parentid = ft.id
)
SELECT * FROM folder_tree
WHERE id = @id

Query output

    SELECT t1.id, 
           t1.parent_id, 
           t1.name,
           t2.name AS parent_name,
           t2.id AS parent_id
      FROM tbl t1
 LEFT JOIN tbl t2 ON t2.id = t1.parent_id
START WITH t1.id = 1 
CONNECT BY PRIOR t1.id = t1.parent_id

WITH JobPathTemp (JobId, ParentId, Level, id_path)
AS
(
  SELECT jobid, 
         parentid, 
         1 as level, 
         '|' + cast(jobid as varchar(max)) as id_path
  FROM job
  WHERE jobid = 1

  UNION ALL

  SELECT  j.JobId, 
          j.parentid, 
          Level + 1, 
          jpt.id_path + '|' + cast(j.jobid as varchar(max))
  FROM Job as j
    INNER JOIN JobPathTemp AS jpt ON j.jobid = jpt.parentid 
                                     AND charindex('|' + cast(j.jobid as varchar), jpt.id_path) = 0
)
SELECT *
FROM JobPathTemp
;