2

Please, tell me an example how to mark all the child nodes to the parent id. Only need to mark those branches whose parent has the value "need" (see example image). Using a recursive query, it is not possible to rename all the children of a particular parent...

Initial data:

+-----+----------+----------+
| id  | parentid | selector |
+-----+----------+----------+
|  1  |          |          |
|  2  | 1        |          |
|  3  | 1        | need     |
|  4  | 2        |          |
|  5  | 2        | need     |
|  6  | 3        |          |
|  7  | 5        |          |
|  8  | 5        |          |
|  9  | 6        |          |
+-----+----------+----------+

Need data:

+-----+----------+----------+----------------+
| id  | parentid | selector | parentSelector |
+-----+----------+----------+----------------+
|  1  | null     |          | null           |
|  2  | 1        |          | null           |
|  3  | 1        | need     | 3              |
|  4  | 2        |          | null           |
|  5  | 2        | need     | 5              |
|  6  | 3        |          | 3              |
|  7  | 5        |          | 5              |
|  8  | 5        |          | 5              |
|  9  | 6        |          | 3              |
+-----+----------+----------+----------------+

The task is to make the grouping by those elements whose parent has the value "need". I think, I should create a column with a mark, as in the example in the table above, or are there any other options?

I use SQL Server 2012

Example

R.Evgeny
  • 39
  • 5

1 Answers1

1

I dont't know if it work on Sql server 2012, but i found this microsoft, i think is what you want, to make the parentSelector with condition, I use CASE (Transact-SQL).

This is another example: stackoverflow question

L. Ros.
  • 114
  • 1
  • 2
  • 9
  • The problem is that with a hierarchical selection, on node 9, we do not know about node 3 and through CASE we can not track this... – R.Evgeny Dec 14 '17 at 08:44
  • If i understood the problem, you need loop join, like this [stackoverflow join](https://stackoverflow.com/questions/29795400/loop-join-in-sql-server-2008). This can permit what you want, but this can make slow the server if the table is bigger than that. – L. Ros. Dec 14 '17 at 10:38
  • I don't think, that is the good solution, because the structure (real tree) more bigger, than this example. But thanks for the help, I will get acquainted with this method – R.Evgeny Dec 14 '17 at 11:12