Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Treeview from sql table

I have sql table like below. I have to show it in tree view

id   parentid     name
1     NULL       outlook
2     1      overcast
3     1       rainy
4     1       sunny
5     2        yes
6     3        wind
7     4      humidity
8     6       strong
9     6        weak
10    7        high
11    8         no
12    9         yes
13   10          no
14   15         yes
15   7        normal

I want output as

-outlook

 - overcast

         - yes

- rainy
     - wind
        - strong
              - no
        - weak
              - yes
-sunny
   - humidity

         -high
               -no
         -normal
               -yes

There is only one root node 'outlook' here.then comes child nodes and sub-child nodes like that.

like image 579
dhanya Avatar asked Jul 19 '11 08:07

dhanya


People also ask

How do you search in TreeView?

TreeView enables you to search for the nodes matching the specified string. To search for a node in TreeView, you can use Search or SearchAll method of C1TreeView class. The Search method takes string as a value, searches for the nodes using depth-first search and returns the node containing the searched string.


1 Answers

WITH    q AS 
        (
        SELECT  *
        FROM    mytable
        WHERE   ParentID IS NULL -- this condition defines the ultimate ancestors in your chain, change it as appropriate
        UNION ALL
        SELECT  m.*
        FROM    mytable m
        JOIN    q
        ON      m.parentID = q.ID
        )
SELECT  *
FROM    q
like image 192
PraveenVenu Avatar answered Sep 28 '22 01:09

PraveenVenu