CODEWITHSUNDEEP
mysqlsqlhierarchical-data
u2425
u2425

Reputation: 61

Sql Query to return leaf nodes?

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

Upvotes: 0

Views: 2564

Answers (2)

Kamil
Kamil

Reputation: 13931

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

Upvotes: 3

PM 77-1
PM 77-1

Reputation: 13334

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

Upvotes: 1

Related Questions