-1

I have a table which contains id, nodeid, nodevalue, and nodelevel columns. Now I want to create new temp column ParentnodeId.

I tried using CTE but not getting the correct result.

Id  Nodeid  NodeLevel   Nodevalue               ParentId
1   100     1           nodevalue1              0
2   200     2           nodevalue2              1
3   300     3           nodevalue3              2
4   400     4           nodevalue4              3
5   500     5           nodevalue5              4
6   600     6           nodevalue6              5
7   700     6           nodevalue7              5
8   800     5           nodevalue8              4
9   900     6           nodevalue9              5
10  1000    5           nodevalue10             4
11  1100    6           nodevalue11             5
12  1200    6           nodevalue12             5
13  1300    6           nodevalue13             5
14  1400    6           nodevalue14             5
15  1500    6           nodevalue15             5
16  1600    5           nodevalue16             4
17  1700    6           nodevalue17             5
18  1800    6           nodevalue18             5
19  1900    7           nodevalue19             6
20  2000    7           nodevalue20             6
21  2100    8           nodevalue21             7
22  2200    9           nodevalue22             8
23  2300    9           nodevalue23             8
24  2400    9           nodevalue24             8
25  2500    7           nodevalue25             6
with parentId as (
    select
      Id,
      Nodeid,
      NodeLevel,
      Nodevalue,
      NodeLevel - 1 as ParentId
    from table1)

, parentnode as (
    select
      Id,
      Nodeid,
      NodeLevel,
      Nodevalue,
      ParentId,
      CAST(NULL as nvarchar(50))as ParentNode
    from parentId as pId
    where NodeLevel = 1
    union all
    select
      pId1.Id,
      pId1.Nodeid,
      pId1.NodeLevel,
      pId1.Nodevalue,
      pId1.ParentId,
      CAST(pn.MruHierarchyNode as nvarchar(50))as ParentNode
    from parentId as pId1
    inner join parentnode as pn on pn.Id = pId1.ParentId)

select * from parentnode
order by Nodeid

I can understand that I wrongly created ParentId column using (nodelevel-1) and that's the issue in the recursive part inner join.

Could anyone help me to get the correct Parentid and ParentNode?

The parent should be the row where:

  • the nodeLevel is the current row's nodeLevel - 1
  • the Id is the highest Id that's less than the current row's Id

For example the id 9 should have the parent id as 8 (if you check the node level) and id 11 to 15 should have the parent id as 10.

sqlfiddle here

closed as unclear what you're asking by Max Vernon, Marco, Philᵀᴹ, dezso, Colin 't Hart Apr 21 '17 at 15:09

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    What are your expected results? – Erik Darling Apr 19 '17 at 15:47
1

Using the SQLFiddle supplied by the OP, and interpreting his comments to mean that the parent of a given is the row where the nodeLevel is one less than the current row's nodeLevel, and where the Id is the highest Id that's still less than the current row's Id, I simply modified the query to get the ParentId so it followed those rules:

with parentId as (
    select
      Id,
      Nodeid,
      NodeLevel,
      Nodevalue,
      p.ParentId
    from table1 c
           CROSS APPLY (SELECT MAX(Id) as ParentId
                          FROM table1
                         WHERE nodeLevel = c.nodeLevel - 1
                           AND Id < c.Id
                       ) p
    )

The results show that the row with Id 9 does indeed have 8 as its parent, and rows where Id = 11 - 16 do indeed have 10 as their parent.

Here's the modified SQLFiddle

  • Thank you very much for the solution! It works correctly!! – mjak Apr 21 '17 at 12:00
1

If you are just attempting to get the lowest Id for the higher parent node you can use the following (note the use of group by and min here, this is so that you don't end up with 85 rows of data caused by multiple rows meeting the join criteria).

Test data:

DECLARE @Nodes TABLE (Id INT, Nodeid INT, NodeLevel INT, NodeValue VARCHAR(25));
INSERT INTO @Nodes
        ( Id ,
          Nodeid ,
          NodeLevel ,
          NodeValue 
        )
VALUES 
(1,100,1,'nodevalue1'),
(2,200,2,'nodevalue2'),
(3,300,3,'nodevalue3'),
(4,400,4,'nodevalue4'),
(5,500,5,'nodevalue5'),
(6,600,6,'nodevalue6'),
(7,700,6,'nodevalue7'),
(8,800,5,'nodevalue8'),
(9,900,6,'nodevalue9'),
(10,1000,5,'nodevalue10'),
(11,1100,6,'nodevalue11'),
(12,1200,6,'nodevalue12'),
(13,1300,6,'nodevalue13'),
(14,1400,6,'nodevalue14'),
(15,1500,6,'nodevalue15'),
(16,1600,5,'nodevalue16'),
(17,1700,6,'nodevalue17'),
(18,1800,6,'nodevalue18'),
(19,1900,7,'nodevalue19'),
(20,2000,7,'nodevalue20'),
(21,2100,8,'nodevalue21'),
(22,2200,9,'nodevalue22'),
(23,2300,9,'nodevalue23'),
(24,2400,9,'nodevalue24'),
(25,2500,7,'nodevalue25') 

The query:

SELECT n1.Id ,
       n1.Nodeid ,
       n1.NodeLevel ,
       n1.NodeValue, 
       ISNULL(MIN(n2.Id), 0) AS ParentNodeId
FROM @Nodes n1
    LEFT JOIN @Nodes n2 ON n1.NodeLevel -1 = n2.NodeLevel
GROUP BY n1.Id ,
         n1.Nodeid ,
         n1.NodeLevel ,
         n1.NodeValue
  • Assumes all rows at nodeLevel 6 are children of the first row at nodeLevel 5. Which is possible, but seems somewhat unlikely.... – RDFozz Apr 19 '17 at 17:13
  • Agreed, but there is no other way to interpret the data based on what OP provided, other than to return all potential matches which blows out the data set. – Nic Apr 19 '17 at 17:42
  • Hence my asking for the rule to be used to identify a parent. That said, I'd actually guess that the parent would probably be the row with the highest ID value (less than the child's ID value) where parent.nodeLevel = child.nodeLevel - 1 – RDFozz Apr 19 '17 at 18:20
  • ...which is, of course, a fairly horrible way to do things in the real world. Of course, this may not be a real world example.... – RDFozz Apr 19 '17 at 18:21
  • 1
    @RDFozz It would be pretty easy to change to get the most recent node by changing it to MAX(n2.Id) and add to the ON clause n1.id > n2.id – Kenneth Fisher Apr 19 '17 at 18:48
0

I've used data supplied on your question.

create table foo (Id int, Nodeid int,  NodeLevel int,  Nodevalue varchar(100));
insert into foo (id, nodeid, nodelevel, nodevalue) values
(1,   100,  1, 'nodevalue1'),--0
(2,   200,  2, 'nodevalue2'),--1
(3,   300,  3, 'nodevalue3'),--2
(4,   400,  4, 'nodevalue4'),--3
(5,   500,  5, 'nodevalue5'),--4
(6,   600,  6, 'nodevalue6'),--5
(7,   700,  6, 'nodevalue7'),--5
(8,   800,  5, 'nodevalue8'),--4
(9,   900,  6, 'nodevalue9'),--5
(10,  1000, 5, 'nodevalue10'),--4
(11,  1100, 6, 'nodevalue11'),--5
(12,  1200, 6, 'nodevalue12'),--5
(13,  1300, 6, 'nodevalue13'),--5
(14,  1400, 6, 'nodevalue14'),--5
(15,  1500, 6, 'nodevalue15'),--5
(16,  1600, 5, 'nodevalue16'),--4
(17,  1700, 6, 'nodevalue17'),--5
(18,  1800, 6, 'nodevalue18'),--5
(19,  1900, 7, 'nodevalue19'),--6
(20,  2000, 7, 'nodevalue20'),--6
(21,  2100, 8, 'nodevalue21'),--7
(22,  2200, 9, 'nodevalue22'),--8
(23,  2300, 9, 'nodevalue23'),--8
(24,  2400, 9, 'nodevalue24'),--8
(25,  2500, 7, 'nodevalue25');--6

Then I've added a new column called ParentId.

ALTER TABLE foo ADD ParentId int NULL;
GO
UPDATE foo
SET    ParentId = NodeLevel -1;
GO
25 rows affected
SELECT * FROM foo;
GO
Id | Nodeid | NodeLevel | Nodevalue   | ParentId
-: | -----: | --------: | :---------- | -------:
 1 |    100 |         1 | nodevalue1  |        0
 2 |    200 |         2 | nodevalue2  |        1
 3 |    300 |         3 | nodevalue3  |        2
 4 |    400 |         4 | nodevalue4  |        3
 5 |    500 |         5 | nodevalue5  |        4
 6 |    600 |         6 | nodevalue6  |        5
 7 |    700 |         6 | nodevalue7  |        5
 8 |    800 |         5 | nodevalue8  |        4
 9 |    900 |         6 | nodevalue9  |        5
10 |   1000 |         5 | nodevalue10 |        4
11 |   1100 |         6 | nodevalue11 |        5
12 |   1200 |         6 | nodevalue12 |        5
13 |   1300 |         6 | nodevalue13 |        5
14 |   1400 |         6 | nodevalue14 |        5
15 |   1500 |         6 | nodevalue15 |        5
16 |   1600 |         5 | nodevalue16 |        4
17 |   1700 |         6 | nodevalue17 |        5
18 |   1800 |         6 | nodevalue18 |        5
19 |   1900 |         7 | nodevalue19 |        6
20 |   2000 |         7 | nodevalue20 |        6
21 |   2100 |         8 | nodevalue21 |        7
22 |   2200 |         9 | nodevalue22 |        8
23 |   2300 |         9 | nodevalue23 |        8
24 |   2400 |         9 | nodevalue24 |        8
25 |   2500 |         7 | nodevalue25 |        6

dbfiddle here

  • Here the parent ids are wrong and I want to create parent id using the Id column. For example the id 9 should have the parent id as 8 and id 11 to 16 should have the parent id as 10 but here for id 9, the parent id is 5 and for id 11 to 15, the parent id is also 5 which is wrong. – mjak Apr 20 '17 at 9:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.