Sql-server – Include parent node ID in hierarchical recordset of child rows

hierarchyrecursivesql server

I have a table of categories which are linked together by a parent_category_id column. In this query I am able to identify all of the child categories (could be several levels of children) for a specified parent, but I would like to also include the parent id on each row.

WITH level_1 (category_id, category_name) AS (
  SELECT
   category_id, 
   category_name
  FROM category
  WHERE 
   parent_category_id = 1
  UNION ALL
  SELECT 
    c.category_id,
    c.category_name
  FROM category AS c CROSS JOIN level_1 
  WHERE 
    c.parent_category_id = level_1.category_id
)
select * from level_1

The query as written returns the following:

category_id | category_name
         64 | Cows
         6  | Pigs
         11 | Holstein
         23 | Bantu

What I want though is below because I want to group on those parent categories but I won't necessarily know what they are except that they have a parent_category_id = 1.

 root_id | category_id |  category_name
      64 |          64 |  Cows
       6 |           6 |  Pigs
      64 |          11 |  Holstein
       6 |          23 |  Bantu

Category table is like below.

   category_id         |  unique row identifier
   category_name       |  varchar
   category_parent_id  |  foreign key to category table

Best Answer

It's hard to understand your requirements without seeing the definition of category - Presumably, there is a root_id column in that table?

If there is, perhaps you want this?

;WITH level_1 (category_id, category_name, root_id) AS (
    SELECT category_id, 
        category_name,
        root_id
    FROM dbo.category
    WHERE parent_category_id = 1
    UNION ALL
    SELECT c.category_id,
        category_name,
        root_id
    FROM dbo.category AS c 
    CROSS JOIN level_1 
    WHERE c.parent_category_id = level_1.category_id
)
SELECT category_id
    , category_name
    , root_id 
FROM level_1;

Just FYI, it's good practice to begin a CTE with a semicolon, to specify the schema, and avoid using SELECT *

Related Solutions

Oracle hierarchical query question (start with … connect by … )

There would have to be data indicating that the row is both a child of row A and a child of row B. This can be done, but does not seem like it would be typical. Think of an employee. Most employees only have one direct managers. In a simple design an employee could have two managers, but it would require multiple employee records. For example:

--drop table e;
create table e (id number, name varchar2(100), manager number);
insert into e values (1,'Amos',null);
insert into e values (2,'Bob',1);
insert into e values (3,'Cindy',1);
insert into e values (4,'Dave',2);
insert into e values (4,'Dave',3);
select substr(name,1,7) name, substr(prior name,1,7) Manager
   from e start with id=1 connect by prior id=manager order by name;

Sql-server – Remove parent and child rows if child or parent fits criteria

This is one way to do it:

IF OBJECT_ID('tempdb..#Orders') IS NOT NULL
    DROP TABLE #Orders;

CREATE TABLE #Orders (
    OrderID int,
    OldOrderID int,
    Action varchar(10),
    EntryDate date,
    Source char(1)
)

-- Your sample data, plus a couple more rows 
--to demonstrate the recursive query
INSERT INTO #Orders
VALUES 
    (    1   ,     NULL      ,   'Insert'  , '2016-01-12',   'A'),
    (    1   ,     NULL      ,   'Remove'  , '2016-01-13',   'A'),
    (    2   ,     NULL      ,   'Insert'  , '2016-01-12',   'B'),
    (    3   ,     NULL      ,   'Insert'  , '2016-01-12',   'C'),
    (    4   ,       3       ,   'Insert'  , '2016-01-13',   'C'),
    (    4   ,     NULL      ,   'Remove'  , '2016-01-14',   'C'),
    (    5   ,       4       ,   'Insert'  , '2016-01-15',   'C'),
    (    5   ,     NULL      ,   'Remove'  , '2016-01-16',   'C');


WITH SourceData AS (
    -- Crosstab to get all orders on a single row
    -- The assumption is you can get just two actions (insert and remove)
    SELECT OrderId, 
        ParentOrder = MAX(OldOrderID), 
        InsertDate = MAX(CASE WHEN Action = 'Insert' THEN EntryDate END), 
        RemoveDate = MAX(CASE WHEN Action = 'Remove' THEN EntryDate END), 
        Source = MAX(Source)
    FROM #Orders
    GROUP BY OrderId
),
OrderHierarchy AS (
    -- Recursive query to get top OrderId
    SELECT *, 
        OrderID AS TopOrderId
    FROM SourceData
    WHERE ParentOrder IS NULL

    UNION ALL

    SELECT SD.*, OH.TopOrderId
    FROM SourceData AS SD
    INNER JOIN OrderHierarchy AS OH
        ON SD.ParentOrder = OH.OrderID
),
AllOrders AS (
    -- Get MAX(RemoveData) for the order chain
    SELECT *, MaxRemovedDate = MAX(RemoveDate) OVER (PARTITION BY TopOrderId)
    FROM OrderHierarchy
)
-- Return just the rows that have no remove date in the chain
SELECT *
FROM AllOrders
WHERE MaxRemovedDate IS NULL;

Related Question