T-SQL Query:Getting Child Nodes of a Parent

T-SQL Query : Getting Child nodes of a parent

Using a recursive Common Table Expression, supported on SQL Server 2005+:

WITH hierarchy AS (
  SELECT yt.id, 
         yt.catid,
         yt.parentcatid,
         yt.siteid
    FROM YOUR_TABLE yt
   WHERE yt.parentcatid = 0
  UNION ALL
  SELECT yt.id, 
         yt.catid,
         yt.parentcatid,
         yt.siteid
    FROM YOUR_TABLE yt
    JOIN hierarchy h ON h.catid = yt.catid)
SELECT t.*
  FROM hierarchy t
OPTION (maxrecursion 1000)

If you get:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion

The default is 100 recursions. The maximum number of recursions can be set via the maxrecursion option, up to a maximum of 32767.

Query to find all the parent and child nodes of a given node in SQL

This should work:

;WITH #results1 AS
(
    SELECT  ChildId, 
            ParentId 
    FROM    History
    WHERE   ChildId= @selected
    UNION ALL
    SELECT  t.ChildId, 
            t.ParentId 
    FROM    History t
            INNER JOIN #results1 r ON r.ExpirationList = t.ParentId 
)
,#results2 AS
(
    SELECT  ChildId, 
            ParentId 
    FROM    History
    WHERE   ChildId= @selected
    UNION ALL
    SELECT  t.ChildId, 
            t.ParentId 
    FROM    History t
            INNER JOIN #results2 r ON t.ExpirationList = r.ParentId 
)
SELECT  *
FROM    #results1
UNION
SELECT  *
FROM    #results2

How to find all parent node by a value in child node with SQL Server

Just use XPath with the predicate you search

set @xml.modify('delete (/Trainings/Training[./Data = "test12"])')

UPDATE: in order to solve also the second issue I propose to use a completely different XML-relational approach:

SELECT c.value('(./Id/node())[1]','int') AS Id,
       c.value('(./Data/node())[1]','nvarchar(max)') AS Data,
       row_number() over (order by c.value('(./Id/node())[1]','int')) as ItemOrder
FROM @xml.nodes('/Trainings/Training') AS A(c)
WHERE c.value('(./Data/node())[1]','nvarchar(max)') != 'test12'
FOR XML PATH('Training'),ROOT('Trainings'),TYPE

demo

Get all child nodes from table

It is because you need the same where statement on the bottom select for tblcategory WHERE [pkID] = 6. With out it you are getting the children of the entire table.

So something like this should work:

SELECT [pkID]
    ,[parentID]
    ,[CategoryName]
FROM [tblCategory]
WHERE [pkID] = 6
UNION ALL
SELECT [Sub].[pkID]
    ,[Sub].[ParentID]
    ,[Sub].[CategoryName]
FROM [tblCategory] [Sub]
INNER JOIN [tblCategory] AS [Sub2] ON [Sub].[ParentID] = [Sub2].[pkID]
WHERE [sub].[pkID] = 6

Simplified as Juan noted and I should have thought of can be done as a single where condition on the same select statement.

SELECT [pkID]
    ,[parentID]
    ,[CategoryName]
FROM [tblCategory]
WHERE [pkID] = 6
     or ParentId = 6

Here is a recursive cte method to get all of the children, grand children, great grand children, etc. of a parentid.

;WITH cte AS (
    SELECT [pkID]
       ,[parentID]
       ,[CategoryName]
       -- ,1 AS [Level]
    FROM [tblCategory]
    WHERE [pkID] = 6

    UNION ALL

    SELECT
       t.pkId
       ,t.[parentID]
       ,t.CategoryName
       -- ,[Level] + 1 AS [Level]
    FROM
       [tblCategory] t
       INNER JOIN cte c
       ON t.ParentId = c.pkId
)

SELECT *
FROM cte  

XQuery - get child node based on parent node

Using this as a reference, try the below query:

select
xx.value(N'(PostalCode)[1]', N'nvarchar(50)') AS PostalCode

from @strazi.nodes(N'/ArrayOfStreetEntity/StreetEntity/PostalNumbers/PostalNo') AS XTbl(xx)
where xx.value('../../Name[1]', 'varchar(max)')='Street name 1'

The above will return as many of child nodes where your street name matches your condition.

How to get all children of a parent and then their children using recursion in query

DECLARE @Id int = your_UnitId
;WITH cte AS 
 (
  SELECT a.Id, a.parentId, a.name
  FROM customer a
  WHERE Id = @Id
  UNION ALL
  SELECT a.Id, a.parentid, a.Name
  FROM customer a JOIN cte c ON a.parentId = c.id
  )
  SELECT parentId, Id, name
  FROM cte

Demo on SQLFiddle

How to get the number of child nodes in a tree in SQL

And another one avoiding count(*)

SELECT
    F.f_name,
    COUNT(1) [number of children]
FROM
    Family F
    JOIN Relations R ON R.r_parent = F.f_id
GROUP BY
    F.f_name

additionally one with using subquery

SELECT
    *
FROM
    (
        SELECT
            F.f_name,
            (SELECT Count(1) FROM Relations R WHERE R.r_parent = F.f_id) [number of children]
        FROM
            Family F
    ) Fathers
WHERE
    Fathers.[number of children] > 0

---

Related Topics