How to Self Join Recursively in SQL

Simplest way to do a recursive self-join?

WITH    q AS 
        (
        SELECT  *
        FROM    mytable
        WHERE   ParentID IS NULL -- this condition defines the ultimate ancestors in your chain, change it as appropriate
        UNION ALL
        SELECT  m.*
        FROM    mytable m
        JOIN    q
        ON      m.parentID = q.PersonID
        )
SELECT  *
FROM    q

By adding the ordering condition, you can preserve the tree order:

WITH    q AS 
        (
        SELECT  m.*, CAST(ROW_NUMBER() OVER (ORDER BY m.PersonId) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
        FROM    mytable m
        WHERE   ParentID IS NULL
        UNION ALL
        SELECT  m.*,  q.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.PersonID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
        FROM    mytable m
        JOIN    q
        ON      m.parentID = q.PersonID
        )
SELECT  *
FROM    q
ORDER BY
        bc

By changing the ORDER BY condition you can change the ordering of the siblings.

SQL Server recursive self join

Recursive cte to the rescue....

Create and populate sample table (Please save us this step in your future questions):

DECLARE @T as table
(
    id int,
    name varchar(100),
    parent_id int
)

INSERT INTO @T VALUES
(1, 'A', NULL),
(2, 'A.1', 1),
(3, 'A.2', 1),
(4, 'A.1.1', 2),
(5, 'B', NULL),
(6, 'B.1', 5),
(7, 'B.1.1', 6),
(8, 'B.2', 5),
(9, 'A.1.1.1', 4),
(10, 'A.1.1.2', 4)

The cte:

;WITH CTE AS
(
    SELECT id, name, name as path, parent_id
    FROM @T 
    WHERE parent_id IS NULL
    UNION ALL
    SELECT t.id, t.name, cast(cte.path +','+ t.name as varchar(100)), t.parent_id
    FROM @T t
    INNER JOIN CTE ON t.parent_id = CTE.id
)

The query:

SELECT id, name, path
FROM CTE

Results:

id      name        path
1       A           A
5       B           B
6       B.1         B,B.1
8       B.2         B,B.2
7       B.1.1       B,B.1,B.1.1
2       A.1         A,A.1
3       A.2         A,A.2
4       A.1.1       A,A.1,A.1.1
9       A.1.1.1     A,A.1,A.1.1,A.1.1.1
10      A.1.1.2     A,A.1,A.1.1,A.1.1.2

See online demo on rextester

How to self JOIN recursively in SQL?

If you are using SQL Server 2005+, you can use common-table expressions

With Family As 
( 
Select s.ID, s.ParentSeriesId, 0 as Depth
From Series s
Where ID = @ParentID 
Union All 
Select s2.ID, s2.ParentSeriesId, Depth + 1
From Series s2
    Join Family 
        On Family.ID = s2.ParentSeriesId 
) 
Select *
From Family 

For more:

Recursive Queries Using Common Table Expressions

Sql Server 2014 - Deep Recursive Parent-Child Self Join

You can try to use DISTINCT to filter duplicate rows.

;WITH CTE AS (
    SELECT Id, ParentId
    FROM T
    WHERE ParentId IS NULL
UNION ALL
    SELECT t.Id, t.ParentId
    FROM T
    INNER JOIN CTE c ON t.ParentId = c.Id
)
SELECT DISTINCT Id, ParentId
FROM CTE

Recursive SQL self join

You want a recursive CTE:

with e as (
      select cast(name as varchar(max)) as list, empId, 0 as level
      from employees
      where managerID is null
      union all
      select e.list + '>' + e2.name, e2.empId, level + 1
      from e join
           employees e2
           on e.empId = e2.managerId
     )
select e.*
from e
where not exists (select 1
                  from employees e2
                  where e2.managerId = e.empId
                 );

CTE Recursion statement with self join without ids

If I understand the issue correctly, you simply need a LAG() and the positions of the separator in the recursive CTE:

Table:

CREATE TABLE ManagerDetails (id int, data varchar(1000))
INSERT INTO ManagerDetails (id, data)
VALUES 
   (1, 'imp/imp2/imp3/imp4'),
   (2, 'notimp1/notimp2/notimp3/notimp4')

Statement:

DECLARE @separator varchar(1) = '/';
WITH rCTE AS(
    SELECT
       d.id,
       1 as [level] ,
       d.data,
       CAST(1 AS int) AS index1,
       CHARINDEX(@separator, CONCAT(d.data, @separator)) AS index2
    FROM ManagerDetails d   
    UNION ALL
    SELECT
       r.id,
       r.[level] + 1,
       r.data,
       CAST(r.index2 + LEN(@separator) AS int),
       CHARINDEX(@separator, CONCAT(r.data, @separator), r.index2 + 1)
    FROM rCTE r
    WHERE CHARINDEX(@separator, CONCAT(r.data, @separator), r.index2 + 1) > 0
)
SELECT
   id AS Id,
   SUBSTRING(CONCAT(data, @separator), 1, index2 - 1) AS [Value],
   LAG(SUBSTRING(CONCAT(data, @separator), index1, index2 - index1)) OVER (PARTITION BY id ORDER BY level DESC) AS [Manager],
   SUBSTRING(CONCAT(data, @separator), index1, index2 - index1) AS [Employee]
FROM rCTE
ORDER BY id, level DESC
OPTION (MAXRECURSION 0)

Result:

Id Value                           Manager Employee
---------------------------------------------------
1  imp/imp2/imp3/imp4                      imp4
1  imp/imp2/imp3                   imp4    imp3
1  imp/imp2                        imp3    imp2
1  imp                             imp2    imp
2  notimp1/notimp2/notimp3/notimp4         notimp4
2  notimp1/notimp2/notimp3         notimp4 notimp3
2  notimp1/notimp2                 notimp3 notimp2
2  notimp1                         notimp2 notimp1

Self join and recursive selection in a table

Here is a SQL statement using Oracle.

select  id, name, role, mgrID
from employees
start with id = 7
connect by NoCycle prior id = mgrid;

Please note that the manager for employee 7 is the employee 7 - they are their own manager. This will cause an error - "Connect By loop in user data'. By using the NoCycle keyword you can tell Oracle to detect this and avoid the error.

Does this solve your issue?

---

Related Topics