an sql question/challenge from a job application exam that i failed to answer

[cookiemonsterthereis]

I just finished a job interview/exam and I failed. :'(  I really don’t know how to implement this problem using an SQL.  You could check the exact question/problem below:

 

There are two tables, NAMES (ID INT IDENTITY (1,1), NAME VARCHAR(255) ) and RELATIONSHIPS (NAMEID INT, PARENT_NAMEID INT) linked via NAMES.ID = RELATIONSHIPS.NAMEID 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 Microsoft 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:

 

- The purpose –  rather than just value – of the LEVEL column

- 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