CODEWITHSUNDEEP
sqlsql-servertreepropagation
Tofnet
Tofnet

Reputation: 404

SQL Server - When selecting how to propagate values on the top level of a tree? (SQL Fiddle included)

I need to propagate values on top level of tree:

CREATE TABLE [dbo].[area](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL,
    [parent_id] [int] NULL,
    [color] [varchar](50) NULL,
    [priority] [int] NULL)

insert into area values('root', NULL, NULL, NULL);
insert into area values('test1', 1, NULL, NULL);
insert into area values('test2', 2, 'red', 50);
insert into area values('test3', 5, 'blue', 1);
insert into area values('test4', 1, 'yellow', 10);
id parent_id name color priority level path_id path_name
1 (null) root (null) (null) 0 1 root
2 1 test1 (null) (null) 1 1\2 root\test1
5 1 test4 yellow 10 1 1\5 root\test4
4 5 test3 blue 1 2 1\5\4 root\test4\test3
3 2 test2 red 50 2 1\2\3 root\test1\test2

I am doing this with the following query (with CTE for next process):

WITH tPATH
AS (SELECT id,
           parent_id,
           name,
           color,
           priority,
           0 as [level],
           CAST(id AS NVARCHAR(MAX)) As path_id,
           CAST(name AS NVARCHAR(MAX)) As path_name
    FROM area
    WHERE parent_id is NULL
    UNION ALL
    SELECT area.id,
           area.parent_id,
           area.name,
           area.color,
           area.priority,
           [level] + 1,
           CONCAT(tPATH.path_id, '\', CAST(area.id AS NVARCHAR(MAX))),
           CONCAT(tPATH.path_name, '\', CAST(area.name AS NVARCHAR(MAX)))
    FROM area
        INNER JOIN tPATH
            ON area.parent_id = tPATH.id
   )
select *
from tPATH;

Priority 1 is the best. So I want this:

id parent_id name color priority level path_id path_name
1 (null) root blue 1 0 1 root
2 1 test1 red 50 1 1\2 root\test1
5 1 test4 blue 1 1 1\5 root\test4
4 5 test3 blue 1 2 1\5\4 root\test4\test3
3 2 test2 red 50 2 1\2\3 root\test1\test2

Any idea to do that (propagate color and priority on top level)? The fiddle || SQLize

Thanks in advance.

Upvotes: 0

Views: 292

Answers (2)

Tofnet
Tofnet

Reputation: 404

Based on @Serg's answer, I created this code which gives me the expected result. Can it be simpler ?

WITH tPATH
AS (SELECT id,
           parent_id,
           name,
           color,
           priority,
           0 [level],
           CAST(id AS NVARCHAR(MAX)) [path_id],
           CAST(name AS NVARCHAR(MAX)) [path_name]
    FROM area
    WHERE parent_id is NULL
    UNION ALL
    SELECT area.id,
           area.parent_id,
           area.name,
           area.color,
           area.priority,
           [level] + 1,
           CONCAT(tPATH.path_id, '\', CAST(area.id AS NVARCHAR(MAX))),
           CONCAT(tPATH.path_name, '\', CAST(area.name AS NVARCHAR(MAX)))
    FROM area
        INNER JOIN tPATH
            ON area.parent_id = tPATH.id
   ),
     tDIFF
AS (SELECT id,
           id [top],
           parent_id,
           priority
    FROM area
    UNION ALL
    SELECT area.id,
           [top],
           area.parent_id,
           area.priority
    FROM area
        INNER JOIN tDIFF
            ON area.parent_id = tDIFF.id
   ),
     tDIFFEND
AS (select [top] as id,
           min(priority) effective_priority
    from tDIFF
    group by [top]
   )
SELECT tPATH.id,
       tPATH.parent_id,
       tPATH.name,
       area.color,
       effective_priority [priority],
       tPATH.level,
       tPATH.path_id,
       tPATH.path_name
from tPATH
    INNER JOIN tDIFFEND
        ON tDIFFEND.id = tPATH.id
    LEFT JOIN area
        ON area.priority = effective_priority;

Upvotes: 0

Serg
Serg

Reputation: 22811

You can compute an effective priority as a minimum over all node desendants with the query

WITH tPATH
AS (SELECT id, 
           id [top],
           parent_id,
           priority
    FROM area

    UNION ALL
    
    SELECT area.id, 
           [top],
           area.parent_id,
           area.priority
    FROM area
    INNER JOIN tPATH
            ON area.parent_id = tPATH.id
   )
select [top] as id, min(priority) effective_priority
from tPATH
group by [top];

Upvotes: 1

Related Questions