0

I have a scenario where my data is visually shown in a tree structure like below.

i.e.

  • Parent Node
    • Child Node 1
      • Child Node 1.1
        • item

In the database, the item record only stores the NodeId of Child Node 1.1. So basically, the item has an indirect relationship with the Parent Node and Child Node 1.

How can I query the tables to return all the records with direct and indirect relationships?

i.e.

ItemId   Name  NodeId
1        Item1   1 
1        Item1   2 
1        Item1   3

http://sqlfiddle.com/#!18/3c092/2

Joshua
  • 2,275
  • 7
  • 41
  • 57
  • 1
    I'm assuming Child Node 1 has a direct relationship to Parent Node? So with 2 hops you can get Parent Node from Child Node 1.1. Maybe recursive CTE - like this https://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example – toni Mar 30 '19 at 21:27
  • Yes the Child Node 1 does have a direct relationship. Just wondering how it could work if it has n number of hops. – Joshua Mar 30 '19 at 21:32
  • I don't know how to solve it in T-SQL if the tree can have any number of levels. I had a similar problem but the tree was never more than 4 levels deep and I solved it with additional fields - ParentID, GrandparentID, etc. If more than 4 levels, I would be doing the recursive work in C#. – toni Mar 30 '19 at 21:39
  • i don't see any relationship column in your table. How are you gong to identify which is the parent record ? – Squirrel Mar 31 '19 at 02:37
  • 1
    read up about nested set models - https://en.wikipedia.org/wiki/Nested_set_model - Basically a left and right node value can solve this - any node with a value between a node's left and right value is a child, and any node that has a left value less and a right value greater is a parent. – Alan Mar 31 '19 at 03:08
  • @Squirrel check the sql fiddle link. Sample schema is there. – Joshua Mar 31 '19 at 07:31
  • There appears to be a problem with your sample data for the Node table. All three records have the same Id. Also, I don't know if that's in the data you are working with or just in your example, but if the ParentId is supposed to be the same as the Id column of the parent record in the Node table, it really should be a foreign key, and the root record with no parent should have null instead of 0 for the value. (If that's the data you have, it is what it is, but I still wanted to point that out.) – Kanmuri Apr 01 '19 at 03:00
  • @Kanmuri It is how I want my result to be. Please check the scheme here http://sqlfiddle.com/#!18/3c092/2. I already have given the link above. Sorry if it is not clear. – Joshua Apr 01 '19 at 12:16

0 Answers0