help needed on tree level sql?

sql , tree , level , db , functions , transact sql Singapore, Singapore
  • 8 years 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 a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“There are only two kinds of languages: the ones people complain about and the ones nobody uses” - Bjarne Stroustrup