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.
NAMES table content:
RELATIONSHIPS table content:
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.
I really would like to know the correct answer for this problem, hope you guys could share your inputs. Thanks guys. :-)