0

Hi i have a Table as follows

ID  ||  NAME ||  PARENTID ||  LEAF  
1   ||   A   ||    NULL   ||   0      
2   ||   B   ||     1     ||   0  
3   ||   c   ||     2     ||   1  
4   ||   A   ||     1     ||   1

Its a kind of tree structure in the data table. I want to retrieve all the leaf nodes (Leaf=1) of a particular parent can any one suggest me sql query for this?

I have tried this:

select ( SELECT LEAF,NAME FROM Tablex WHERE PARENTID=1) from Tablex where LEAF=1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
u2425
  • 61
  • 9
  • 1
    See [What is the most efficient/elegant way to parse a flat table into a tree?](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/192462#192462) – Bill Karwin Feb 28 '13 at 20:15
  • what result you want get ?can you show how you want the reuslts? – echo_Me Feb 28 '13 at 20:23
  • 1
    I added few useful links to my answer. – Kamil Feb 28 '13 at 20:29

2 Answers2

3

Tree structure in databases is a very common problem.

There are many possible approaches.

SQL (in MySQL) is not the best way to handle tree structures, because tree structure may have unlimited levels, and there is no (effective) way to handle recurrency like this in MySQL, because you have to JOIN table n-times, where n is number of tree levels. When number of levels may vary - you have to use some stored procedure in PL/SQL (procedural SQL), which will be much less effective (slower and will use more memory) than simple loop in PHP, C or other language.

Please consider transforming table into tree with other language (PHP or other language you are using).

As Gordon Linoff and ypercube wrote in comments, there are more advanced databases (like Oracle) and handling trees in these databases is more effective and comfortable in programming/coding.

Additional information:

Managing Hierarchical Data in MySQL (by Mike Hillyer)

Google search: sql tree structure

PHP approach:

php / Mysql best tree structure

PHP tree structure for categories and sub categories without looping a query

And one more useful link from ypercube:

Models for hierarchical data - presentation by Bill Karwin / Karwin Software Solutions LLC

Community
  • 1
  • 1
Kamil
  • 13,363
  • 24
  • 88
  • 183
  • yeah that's a better one for me to head to solution. Thanks,i will find it – u2425 Feb 28 '13 at 19:44
  • 1
    @Kamil . . . I'm almost tempted to vote this down (which I very very rarely do) because of the statement: "SQL is not the best way to handle tree structures, because tree structure may have unlimited levels, and there is no (effective) way to handle recurrency like this in SQL language". Several databases do support tree structures very well. Your statement is true of MySQL but not true of RDBMS in general. – Gordon Linoff Feb 28 '13 at 19:47
  • 1
    @GordonLinoff: `Several databases do support tree structures very well.` -- Yes, but with *vendor-specific extensions,* not in SQL92 proper. – Robert Harvey Feb 28 '13 at 19:48
  • @RobertHarvey . . . The statement that you have to use PL/SQL, for instance, is false. Oracle provides the `connect by` syntax for their SQL. – Gordon Linoff Feb 28 '13 at 19:50
  • @GordonLinoff: Is that ANSI SQL92 compliant? Anyway, I see what you mean. – Robert Harvey Feb 28 '13 at 19:51
  • @GordonLinoff I think you are (a little) wrong. There are databases able to handle non-relational data like trees, olap cubes, maybe other data models effectively, but they are not "pure" relational databases. RDBMS is Relational DataBase Management System. Your example - Oracle is far beyond definition of RDBMS. – Kamil Feb 28 '13 at 19:54
  • @GordonLinoff yes, Oracle provides `connect by`, but we are talking about MySQL. Probably you downvoted my answer about MySQL, because i didnt considered Oracle... – Kamil Feb 28 '13 at 20:00
  • @Kamil: You are wrong and Gorgon is right. Recursive CTEs are standard SQL (not ANSI/ISO SQL-92 but ANSI/ISO SQL-2003+) and are supported by many DBMS, like Oracle, SQL-Server, Postgres and (I think by DB2 as well). – ypercubeᵀᴹ Feb 28 '13 at 20:00
  • There is nothing wrong. You state ***"SQL is not the best way to handle tree structures"***. Please correct your answer if you mean MySQL only. – ypercubeᵀᴹ Feb 28 '13 at 20:02
  • 1
    You have my upvote now. You could also add a link to [Bill Karwin's](http://stackoverflow.com/users/20860/bill-karwin) slideshow about **[Hierarchical models in MySQL](http://www.slideshare.net/billkarwin/models-for-hierarchical-data)**, where 4 models are compared (with more focus on the **Closure** model.) – ypercubeᵀᴹ Feb 28 '13 at 20:13
1

The query that you tried should look something like this:

select tt.* FROM 
( SELECT LEAF,NAME FROM Tablex WHERE PARENTID IS NOT NULL and PARENTID=1 ) AS tt 
WHERE leaf=1;

Here's SQL Fiddle if you want to play with it further: http://sqlfiddle.com/#!2/2d95b8/2

PM 77-1
  • 12,933
  • 21
  • 68
  • 111