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

[cookie77monster]

I just finished a job interview/exam and I failed. I really don’t know how to implement this problem using an MS 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

I really would like to know the correct answer for this problem, hope you guys could share your inputs. Thanks guys. :-)

[Ramy Mahrous]

I'll share with you and your question would be answered, but give me your approaches, how could you think in this problem

[cookie77monster]

I'll share with you and your question would be answered, but give me your approaches, how could you think in this problem

to tell you honestly sir, what I only know is how to create tables, select, delete and other basic sql commands. I am also not knowledgeable in MS SQL.

Also, i think based on the example, the RELATIONSHIP table used a foregin key to access the NAME table. But going back on the question, the requriement for the primary key was not indicated or not included.

[Ramy Mahrous]

OK, friend...
You should read in Joins, LevelID (ID of Parent) if you need me to send you the SELECT statement, it's OK; but I see you won't gain any benefits.

[cookie77monster]

OK, friend...
You should read in Joins, LevelID (ID of Parent) if you need me to send you the SELECT statement, it's OK; but I see you won't gain any benefits.

Sir, may I see the select statement that you're talking about?

[Ramy Mahrous]

SELECT Relationships.Parent_NameID AS [level], Names.ID, Names.Name
FROM Relationships FULL OUTER JOIN Names ON Relationships.NameID = Names.ID