Assume I've got a table which has the next structure:
================= | Id | ParentId | ================= | 1 | 0 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 2 | | 6 | 3 | | 7 | 3 | -----------------
This produces a tree such as this:
1 / \ 2 3 / \ / \ 4 5 6 7
Given an id, how do you get all of the leaf nodes? So, when the given id is 2, the return ought to be 4 &lifier 5. The given id should never be a leaf node itself.
I am unsure how you can customize the SQL here: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
SELECT t1.name FROM category AS t1 LEFT JOIN category as t2 ON t1.category_id = t2.parent WHERE t2.category_id IS NULL;
EDIT1: Also, how do you obtain the root id for any given id? So, when the given id is 2, the return ought to be 1.
"Also, how do you obtain the root id for any given id? So, when the given id is 2, the return ought to be 1."
That's pretty easy, this is the ParentId!
You will get the kids using the following query:
SELECT child.Id FROM theTable current LEFT JOIN theTable child ON child.ParentId = current.Id;
Btw, I would not recommend using
0 once the row doesn't have parent, I'd rather use
NULL for the reason that situation.