Community discussion forum

help needed on tree level sql?

• • Reply

  7 months ago

  by kareen

  snoopy peanuts

  Singapore, Singapore

  Joined 7 months ago
  There are 2 tables, Names(id int identity(1,1), name varchar(255)) and Relationships (nameid int, parent_nameid int) linked via names.id = Relationships.namesid and where top-most name has a parent_nameid = 0. Show a nested list of names including level, nameid and name where level indicates the nest level (or depth) from the top, as indicated in the expected output (below). You may use functions, stored procedures, views and any other Transact SQL commands compliant with MS SQL 2000. Sample Data. Names table content: id name 1 Frank 2 Jo 3 Mary 4 Peter 5 Amy Relationships table content: nameid parent_nameid 1 0 2 1 3 2 4 1 5 2 Expected Output In the expected output, please note 1. The purpose - rather than just the value - of the Level column 2. The sequence of the output, in which child elements appear immediately beneath their respective parent elements. level id name 0 1 Frank 1 2 Jo 2 5 Amy 2 3 Mary 1 4 Peter Please help ASAP, as Im unable to get the above expected output when using recursive SQL method (using the With command)! The closest so far to I got is the following query with the results as follows which is different than the expected output: With temp_family (Parent, Child, Name, iteration) AS ( select Parent_NameID, ID, Name, 0 from Names n, Relationships r where ID = NAMEID and Parent_NameID=0 UNION ALL select Parent_NameID, ID, n.Name, f.iteration + 1 from temp_family f, Names n, Relationships r where ID = NameID and f.Child = r.Parent_NameID ) Select Parent as Level, Child as ID, Name from temp_family; Level ID Name 0 1 Frank 1 2 Jo 1 4 Peter 2 3 Mary 2 5 Amy
  Post was edited on 07/04/2009 05:35:28 Report abuse