Select Statement to Return Parent and Infinite Children

Select statement to return parent and infinite children

So referencing this answer:

SQL Server CTE Parent Child recursive

Here's a working version with your schema:

Table Creation Script

CREATE TABLE YOUR_TABLE
    ([ID] int, [ParentID] int, [Name] varchar(21))
;
    
INSERT INTO YOUR_TABLE
    ([ID], [ParentID], [Name])
VALUES
    (1, NULL, 'A root'),
    (2, NULL, 'Another root'),
    (3, 1, 'Child of 1'),
    (4, 3, 'Grandchild of 1'),
    (5, 4, 'Great grandchild of 1'),
    (6, 1, 'Child of 1'),
    (7, NULL, 'Another root'),
    (8, 7, 'Child of 6')
;

Recursive CTE

DECLARE @ID INT = 1

;WITH ParentChildCTE
AS (
    SELECT ID, ParentId, Name        
    FROM YOUR_TABLE
    WHERE Id = @ID

    UNION ALL

    SELECT T1.ID, T1.ParentId, T1.Name        
    FROM YOUR_TABLE T1
    INNER JOIN ParentChildCTE T ON T.ID = T1.ParentID
    WHERE T1.ParentID IS NOT NULL
    )
SELECT *
FROM ParentChildCTE

The key part is in the CTE creation where the UNION ALL joins back on to the result set, joining ID to ParentId, which doesn't limit the number of level.

Selecting children from an infinite hierarchy

Here is an example with an extra field Name, but with CTE the recursion is simple:

DECLARE @ID int

SET @ID = 1;

WITH CTE_Table_1
(
  ID,
  Name,
  ParentID,
  TreeLevel
)
AS(
 SELECT 
  ID,
  Name,
  ParentID,
  0 AS TreeLevel
 FROM Table_1
 WHERE ID = @ID

UNION ALL

 SELECT 
  T.ID,
  T.Name,
  T.ParentID,
  TreeLevel + 1
 FROM Table_1 T
 INNER JOIN CTE_Table_1 ON CTE_Table_1.ID = T.ParentID
)

SELECT * FROM CTE_Table_1

CTE to get all children and nested children of every parent

In first part of cte select all the rows with ownid as root id. Then in second part (after union all) select parentid as rootid.

Schema and insert statements:

 create table users (UserID int,  ParentID int);
 insert into users values (1,        NULL);
 insert into users values (2,        1);
 insert into users values (3,        1);
 insert into users values (4,        2);
 insert into users values (5,        2);
 insert into users values (6,        5);
 insert into users values (7,        6);
 insert into users values (8,        6);
 insert into users values (9,        NULL);

Query:

 with cte as
 (
   select userid rootid, userid, parentid from users 
   union all
   select cte.rootid rootid, users.userid, users.parentid from users
   inner join cte on users.parentid=cte.userid
 )
 select rootid parentid,userid from cte
 order by rootid ,userid
 option (maxrecursion 0)

Output:

parentid	userid
1	1
1	2
1	3
1	4
1	5
1	6
1	7
1	8
2	2
2	4
2	5
2	6
2	7
2	8
3	3
4	4
5	5
5	6
5	7
5	8
6	6
6	7
6	8
7	7
8	8
9	9

Get All records with parent Child relationship to the Nth Level

You can do this with the help of common table expression

declare @companyId int
set @companyId = 1
;WITH cte
AS
(
    SELECT CompanyId, CompanyName, ParentCompanyId, 0 as steps
    FROM dbo.tblCompany
    --WHERE ParentCompanyId IS NOT NULL
     WHERE companyid = @companyId
  UNION ALL
    SELECT  c.CompanyId, c.CompanyName, c.ParentCompanyId, cte.steps +1 as steps
    FROM dbo.tblCompany AS c
    inner JOIN cte ON cte.CompanyId = c.ParentCompanyId
)
SELECT CompanyId, CompanyName, ParentCompanyId, steps
FROM cte;

In the above query step is the level in the hierarchy.

What is the query to get parent records against specific child or get child records against parent?

If I understand your question correctly that you don't want to insert null values in Parent_ID column then you should replace NULL with 0 and your updated code will be like:

;WITH DATA AS (
                SELECT p.PERSON_ID,p.Name, p.PARENT_ID
                FROM hierarchy p
                WHERE p.PERSON_ID = 9
                UNION ALL
                SELECT c.PERSON_ID,c.Name, c.PARENT_ID
                FROM hierarchy c
                JOIN DATA h 
                ON c.PERSON_ID = h.PARENT_ID 
          )
select * from DATA;

How do I select one parent row and additional rows for its children without a UNION?

Have a dirty DIRTY hack:

SELECT
    P2.Name Parent_Name,
    C.Name  Child_Name
FROM [Parent Table] P1
    FULL OUTER JOIN [Child Table] C
        ON 1=0
    INNER JOIN [Parent Table] P2
        ON IsNull(P1.ID,C.ParentId) = P2.ID
WHERE P2.ID = *ID here*

This should give you the results you want... hopefully.

Show all Children and Grandchildren in SQL Hierarchy CTE

What you are trying to do resembles, at least to some extent, the Ranganathan's classification. In this case you have to go up in hierarchy, not down:

with cte as (
    select t.ChildId, t.ParentId, 0 as [Lvl]
    from @Table t
    where t.ParentId is not null
    union all
    select c.ChildId, t.ParentId, c.Lvl + 1
    from @Table t
        inner join cte c on c.ParentId = t.ChildId
    where t.ParentId is not null
)
select * from cte c order by c.ChildId, c.Lvl, c.ParentId;

EDIT: updated the WHERE clause in the recursive part of the CTE. Looks like it was some leftover from initial tries, which I forgot to think through..

Recursive query for postgresSQL parent/child

A recursive query is not needed to find the deepest children. Instead, one would look at entries that are not a parent (so no other child exists). Such entries ID is not included in the parent column.

You can then join this categories to other tables

SELECT *
FROM category cat
  JOIN category_dictionary cat_dic ON cat.id = cat_dic.id
WHERE NOT EXISTS
 (SELECT 1 FROM category cat2
  WHERE cat2.parent = cat.id);

Select Statement to Return Parent and Infinite Children