Getting Root & Leaf Nodes for a Given Id - How to?

Question

Assume I have a table that has the following structure:

=================
| Id | ParentId |
=================
| 1  | 0        |
| 2  | 1        |
| 3  | 1        |
| 4  | 2        |
| 5  | 2        |
| 6  | 3        |
| 7  | 3        |
-----------------

This results in a tree like this:

     1
    / \
  2     3
 / \   / \
4   5 6   7

Given an id, how do I get all the leaf nodes? So, if the given id is 2, the return should be 4 & 5. The given id will never be a leaf node itself.

I'm not sure how to modify 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 I get the root id for a given id? So, if the given id is 2, the return should be 1.

Answer 1

"Also, how do I get the root id for a given id? So, if the given id is 2, the return should be 1."

That's pretty easy, that's the ParentId!

You'll get the children with the following query:

SELECT child.Id FROM theTable current LEFT JOIN theTable child ON child.ParentId = current.Id;

Btw, I wouldn't recommend using 0 when the row has no parent, I'd rather use NULL in that case.