How to get this nested level output
-
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). 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 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 any sounds please...
Ramana
-
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). 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 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 any sounds please...
Ramana
-
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). 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 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 any sounds please...
Ramana
Have a look at: Recursive Queries Using Common Table Expressions[^]
The need to optimize rises from a bad design. My articles[^]