SQL Server: How to Get All Child Records Given a Parent Id in a Self Referencing Table

SQL Server: How to get all child records given a parent id in a self referencing table

You can try this

DECLARE @Table TABLE(
        ID INT,
        ParentID INT,
        NAME VARCHAR(20)
)

INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 1, NULL, 'A'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 2, 1, 'B-1'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 3, 1, 'B-2'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 4, 2, 'C-1'
INSERT INTO @Table (ID,ParentID,[NAME]) SELECT 5, 2, 'C-2'

DECLARE @ID INT

SELECT @ID = 2

;WITH ret AS(
        SELECT  *
        FROM    @Table
        WHERE   ID = @ID
        UNION ALL
        SELECT  t.*
        FROM    @Table t INNER JOIN
                ret r ON t.ParentID = r.ID
)

SELECT  *
FROM    ret

How to get all child of each records of a self-referenced table

Use this query.

Updated Result: Fiddler Demo

    CREATE TABLE TABLE1 (ID INT, ParentID INT, NAME VARCHAR(10));
INSERT INTO TABLE1 VALUES(1, NULL, 'Name1');
INSERT INTO TABLE1 VALUES(2, 1, 'Name2');
INSERT INTO TABLE1 VALUES(3, 2, 'Name3');
INSERT INTO TABLE1 VALUES(4, 3, 'Name4');
INSERT INTO TABLE1 VALUES(5, 4, 'Name5');
INSERT INTO TABLE1 VALUES(6, 5, 'Name6');
INSERT INTO TABLE1 VALUES(7, 6, 'Name7');
INSERT INTO TABLE1 VALUES(8, 7, 'Name8');
INSERT INTO TABLE1 VALUES(9, 8, 'Name9');
INSERT INTO TABLE1 VALUES(10, 9, 'Name10');
INSERT INTO TABLE1 VALUES(11, 3, 'Name11');

  SELECT ID, ParentID , Name, Child = STUFF((
          SELECT ',' + CAST(B.ID AS VARCHAR(100)) FROm TABLE1 AS B 
          WHERE ISNULL(A.ID, 0) < B.ID AND B.ID NOT IN(
              ISNULL((SELECT TOP 1 C.ID FROm TABLE1 AS C 
                      WHERE C.ParentID IN (
                            SELECT ParentID FROM TABLE1 WHERE ID <= A.ID) 
                      ORDER BY C.ID DESC), 0))  
          ORDER BY B.ID DESC 
          FOR XML PATH (''), type).value('.', 'varchar(max)'), 1,1,'')
 FROm TABLE1 AS A

Old Result:

 SELECT ID, ParentID , Name, ParentIDs = STUFF((
  SELECT ',' + CAST(B.ID AS VARCHAR(100)) FROm MyTable AS B 
  WHERE ISNULL(A.ID, 0) < B.ID ORDER BY B.ID  DESC FOR XML PATH (''), type).value('.',
          'varchar(max)'), 1,1,'')
 FROm MyTable AS A

Find topmost parents in self-referential table

2 issues with the code:

1. You need to track your starting ID through the recursion;
2. The where condition in the recursive part is actually preventing you from getting the result.

As such:

WITH cte AS (
    SELECT ID, link, ID as [StartID]
    FROM @t
    WHERE ID IN (6, 7)
    UNION ALL
    SELECT t.ID, t.link, c.StartID
    FROM @t t
    JOIN cte c ON t.ID = c.link
)
SELECT c.StartID, c.ID
FROM cte c
where c.link is null;

How to get child data from a self referencing table in sql

Assuming that the depth of the category tree is not more than 3 levels, this should work:

declare @Catergory table (
    ID int not null,
    Name nvarchar(10) not null,
    ParentID int null
)
declare @Customer table (
    ID int not null,
    Name nvarchar(10) not null,
    SurName nvarchar(10) not null,
    Address nvarchar(30) not null,
    CategoryId int not null
)
insert into @Catergory (ID, Name, ParentID)
values (1, 'A', null), (2, 'B', null),
    (3, 'C', null), (4, 'AA', 1),
    (5, 'CC', 3), (6, 'AAA', 4),
    (7, 'BB', 2), (8, 'AAA', 4),
    (9, 'CCC', 5), (10, 'AA', 1)

insert into @Customer (ID, Name, SurName, Address, CategoryId)
values (1, 'Duck', 'Duffy', '10 Steet', 10),
 (2, 'Ben', 'Ten', '10 Steet', 6),
 (3, 'Cat', 'Dog', '10 Steet', 5),
 (4, 'Chicken', 'Wings', '10 Steet', 1),
 (5, 'Fish', 'Water', '10 Steet', 7)

-- build structure using assumption that the depth is max three levels

select *
from @Customer cust
    join (
        select ID, Name as CategoryName, null As CategoryType, null as SubCategory from @Catergory roots where ParentID is null
        union
        select mids.ID, roots.Name, mids.Name, null from @Catergory mids
            join @Catergory roots on mids.ParentID = roots.ID and roots.ParentID is null
        union
        select leafs.ID, roots.Name, mids.Name, leafs.Name from @Catergory leafs
            join @Catergory mids on leafs.ParentID = mids.ID 
            join @Catergory roots on mids.ParentID = roots.ID and roots.ParentID is null
    ) as struct on cust.CategoryId = struct.ID
order by cust.id

Output:

+----+---------+---------+----------+------------+----+--------------+--------------+-------------+
| ID |  Name   | SurName | Address  | CategoryId | ID | CategoryName | CategoryType | SubCategory |
+----+---------+---------+----------+------------+----+--------------+--------------+-------------+
|  1 | Duck    | Duffy   | 10 Steet |         10 | 10 | A            | AA           | NULL        |
|  2 | Ben     | Ten     | 10 Steet |          6 |  6 | A            | AA           | AAA         |
|  3 | Cat     | Dog     | 10 Steet |          5 |  5 | C            | CC           | NULL        |
|  4 | Chicken | Wings   | 10 Steet |          1 |  1 | A            | NULL         | NULL        |
|  5 | Fish    | Water   | 10 Steet |          7 |  7 | B            | BB           | NULL        |
+----+---------+---------+----------+------------+----+--------------+--------------+-------------+

Some extra columns are still in there, but I'm sure you can get rid of them. Note though that some of the cartegory columns have null values. This is because if a customer is in a top or mid level category, there is no reasonable way to fill out those columns.

SQL to retrieve parent-child relationship in parent-child order, from a self referencing table

What about to append an 'order' field? This may be an approach:

WITH ChildLocations(LocationId, FkParentLocationId, [Level]) 
    AS 
    (
        (
            -- Start CTE off by selecting the home location of the user
            SELECT l.LocationId, l.FkParentLocationId, 0 as [Level],
                   cast( str( l.locationId ) as varchar(max) ) as orderField
            FROM   Location l
            WHERE  l.LocationId = @locationId
        )
        UNION ALL 
        -- Recursively add locations that are children ...
        SELECT l2.LocationId, l2.FkParentLocationId, [Level] + 1,
               tmp.orderField + '-' + 
               str(tmp.locationId) as orderField
        FROM   ChildLocations tmp
               INNER JOIN Location l2
                    ON  l2.FkParentLocationId = tmp.LocationId
    )
SELECT * from ChildLocations order by orderField;

Remember than Order by in an Insert is not allowed.

Take a look a sample

Get all subitems (children, grandchildren) and parents from a self referencing table

Try using the following recursive CTE as a basis for your solution:

WITH TREE AS (
  SELECT     ROOT.Id        
            ,ROOT.ParentId  
            ,ROOT.Prop1
            ,ROOT.Prop2 
            ...
            ,ROOT.PropN                 
            ,1 AS Level 
  FROM       ENTITIES ROOT      
  -- list entities starting from ROOT node (no ParentId)
  WHERE      ROOT.ParentId IS NULL 
  -- or list entities starting from a specified node
  -- WHERE ROOT.Id = @entityId

  UNION ALL

  SELECT     CHILD.Id
            ,CHILD.ParentId 
            ,CHILD.Prop1
            ,CHILD.Prop2    
            ...
            ,CHILD.PropN                
            ,PARENT.Level + 1
  FROM       ENTITIES CHILD INNER JOIN 
             TREE PARENT ON CHILD.ParentId = PARENT.Id
  WHERE      CHILD.ParentId IS NOT NULL
)

SELECT *  FROM TREE

You could place the CTE inside a VIEW or a Stored Procedure if you need to pass parameters.

SQL Query to get records of parent table that have a list of child records

This will return all Parent records which have [at least] one child with a 'fizz' foo AND [at least] one child with 'buzz' foo. Which is what I think is required in the question.

Also, while being potentially sub-optimal, this query is generic in a sense that it will work with most SQL implementation, not only the more modern ones, where the support of CTE, subqueries and related constructs are supported.

   SELECT DISTINCT Parent.ID
    FROM Parent
    JOIN Child C1 ON Parent.ID = C1.parent_Id
    JOIN Child C2 ON Parent.ID = C2.parent_id
    WHERE C1.foo = 'fizz'
      AND C2.foo = 'buzz'

Edit:

Now that Joel Potter has fixed the query in his answer, we probably agree that his approach has several advantages over the query listed above (please give him a few +reps). In particular:

• the structure of the query doesn't change when we add, or remove, targeted values for the column foo.
• the query is probably more easily optimized [by the server itself]
• the structure of the query allows it to handle variations on the definition of the filter. For example we can query all Parents which have children such that say 2 of 5 possible values of foo are present.

Following is Joel's query here, slightly modified, to show how it can be expanded for more than 2 foo values.

SELECT Parent.Id
FROM Parent
INNER JOIN Child on Parent.Id = child.parent_id
WHERE Child.foo IN ('fizz', 'buzz')  -- or say, ... IN ('fizz', 'buzz', 'bang', 'dong')
GROUP BY Parent.Id
HAVING COUNT(DISTINCT Child.foo) = 2  -- or 4 ...  

return parent-chain ids of self-reference MSSQL table

;with parents as
(
    select 
        StuffID, ParentID
    from 
        [Stuff]
    WHERE 
        [Name] LIKE '%rout%'
    union all
    select
        s.StuffID, s.ParentID
    from
        [Stuff] s
    join parents p on
        s.StuffID = p.ParentID
)
SELECT 
    StuffID 
from 
    parents
order by 
    StuffID

The output will be: 1 2 7 13 14 (differs from your expectation 1 2 9 13 14, is that a mistake?)

---

Related Topics