Simplest way to do a recursive self-join?

user_68329 0
Viewed 122980

What is the simplest way of doing a recursive self-join in SQL Server? I have a table like this:

PersonID | Initials | ParentID
1          CJ         NULL
2          EB         1
3          MB         1
4          SW         2
5          YT         NULL
6          IS         5

And I want to be able to get the records only related to a hierarchy starting with a specific person. So If I requested CJ's hierarchy by PersonID=1 I would get:

PersonID | Initials | ParentID
1          CJ         NULL
2          EB         1
3          MB         1
4          SW         2

And for EB's I'd get:

PersonID | Initials | ParentID
2          EB         1
4          SW         2

I'm a bit stuck on this can can't think how to do it apart from a fixed-depth response based on a bunch of joins. This would do as it happens because we won't have many levels but I would like to do it properly.

Thanks! Chris.

self-join hierarchical-data recursion sql-server sql
6 Answers

Check following to help the understand the concept of CTE recursion

DECLARE
@startDate DATETIME,
@endDate DATETIME

SET @startDate = '11/10/2011'
SET @endDate = '03/25/2012'

; WITH CTE AS (
    SELECT
        YEAR(@startDate) AS 'yr',
        MONTH(@startDate) AS 'mm',
        DATENAME(mm, @startDate) AS 'mon',
        DATEPART(d,@startDate) AS 'dd',
        @startDate 'new_date'
    UNION ALL
    SELECT
        YEAR(new_date) AS 'yr',
        MONTH(new_date) AS 'mm',
        DATENAME(mm, new_date) AS 'mon',
        DATEPART(d,@startDate) AS 'dd',
        DATEADD(d,1,new_date) 'new_date'
    FROM CTE
    WHERE new_date < @endDate
    )
SELECT yr AS 'Year', mon AS 'Month', count(dd) AS 'Days'
FROM CTE
GROUP BY mon, yr, mm
ORDER BY yr, mm
OPTION (MAXRECURSION 1000)
user_15865170 
DELIMITER $$

 

DROP PROCEDURE IF EXISTS `myprocDURENAME`$$

CREATE DEFINER=`root`@`%` PROCEDURE `myprocDURENAME`( IN grp_id VARCHAR(300))
BEGIN
   SELECT h.ID AS state_id,UPPER(CONCAT( `ACCNAME`,' [',b.`GRPNAME`,']')) AS state_name,h.ISACTIVE  FROM accgroup b JOIN (SELECT get_group_chield (grp_id) a) s ON FIND_IN_SET(b.ID,s.a) LEFT OUTER JOIN acc_head h ON b.ID=h.GRPID WHERE h.ID IS NOT NULL AND H.ISACTIVE=1;
    END$$

DELIMITER ;

////////////////////////

DELIMITER $$

 

DROP FUNCTION IF EXISTS `get_group_chield`$$

CREATE DEFINER=`root`@`%` FUNCTION `get_group_chield`(get_id VARCHAR(999)) RETURNS VARCHAR(9999) CHARSET utf8
BEGIN
     DECLARE idd VARCHAR(300);
   DECLARE get_val VARCHAR(300);
    DECLARE get_count INT;
SET idd=get_id;
   
SELECT  GROUP_CONCAT(id)AS t,COUNT(*) t1 INTO get_val,get_count FROM accgroup ag JOIN (SELECT idd AS n1) d ON FIND_IN_SET(ag.PRNTID,d.n1);
SELECT   COUNT(*) INTO get_count FROM accgroup WHERE PRNTID IN (idd);
 WHILE get_count >0 DO
 SET idd=CONCAT(idd,',', get_val); 
SELECT  GROUP_CONCAT(CONCAT('', id ,'' ))AS t,COUNT(*) t1 INTO get_val,get_count FROM accgroup ag JOIN (SELECT get_val AS n1) d ON FIND_IN_SET(ag.PRNTID,d.n1);
   END WHILE;
   RETURN idd;
-- SELECT id FROM acc_head WHERE GRPID  IN (idd);
    END$$

DELIMITER ;
user_166946930
Related