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
Enter your message below
Sign in or Join us (it's free).