I have table structure like this:
ID Name ParentId ------------------------- 1 Root 0 2 Business 1 3 Finance 2 4 Stock 3 5 Any 3 6 One 0 7 Two 6
Is there any way to get all relation in one line?
Like this:
+------------------------------------------------------------------------------------+ |ID | NAME | ID | NAME | ID | NAME | ID | NAME | |----------|------------|-----------|-----------|----------|---------|--------|------| | 1 | Root | 2 | Business | 3 | Finance | 4 | Stock| | 1 | Root | 2 | Business | 3 | Finance | 4 | Any | | 1 | One | 6 | Two | | | | |
Advertisement
Answer
Assuming your table has a name “htable” and as soon as there are up to FOUR (4) levels of hierarchy, it can be done like that using self join:
SELECT level1.ID, level1.Name, level2.ID, level2.Name, level3.ID, level3.Name, level4.ID, level4.Name FROM htable AS level1 LEFT JOIN htable AS level2 ON level1.ID = level2.ParentId LEFT JOIN htable AS level3 ON level2.ID = level3.ParentId LEFT JOIN htable AS level4 ON level3.ID = level4.ParentId;