Tree traversal SQL query
Anyone, please help me with the SQL query for traversing the tree.
I have a ta开发者_JAVA百科ble called users
, which has the columns id
, name
, and parent
.
Please, help me with a SQL query that can traverse the tree from the desired root.
EDIT Hi i got this URL that seems to be solved.
Deeply nested subqueries for traversing trees in MySQL
But i don't have knowledge of JOIN, Thats y i cant construct the query with my table :-( :-(
MySQL does not support recursive queries. Most other brands of database support recursive query syntax with the common table expression syntax (queries beginning with WITH
).
To solve tree traversal queries in MySQL, you need to store the data differently so you can query whole trees in one query. There are several solutions for this:
- Path Enumeration
- Nested Sets
- Closure Table
For details, see:
- What is the most efficient/elegant way to parse a flat table into a tree?
- Models for Hierarchical Data with SQL and PHP
- SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming
精彩评论