Getting root and leaf nodes for a given identifier - how?

Question

Suppose 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        |
-----------------

The result is a tree:

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

Given id, how do I get all leaf nodes? So, if the given id is 2, the return should be 4 and 5. This identifier will never be a leaf node.

I'm not sure how to change SQL here: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

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 to get the root id for a given id? So, if the given identifier is 2, the return should be 1.

Answer 1

", ? , 2, 1."

, ParentId!

:

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

Btw, 0, , NULL .