Андрей

SELECT `SC_categories`.* FROM `SC_categories` LEFT JOIN (SELECT DISTINCT(`parent`) FROM `SC_categories`) AS `list` ON (`SC_categories`.`category_ID` = `list`.`parent`) WHERE `list`.`parent` IS NULL

вроде ничего не перепутал..

UPD1:
да, проверил, работает

mysql> select * from `SC_categories`;
+-------------+--------+
| category_ID | parent |
+-------------+--------+
|           1 |      0 |
|           2 |      1 |
|           3 |      2 |
|           4 |      0 |
|           5 |      4 |
|           6 |      2 |
|           7 |      6 |
+-------------+--------+
7 rows in set (0,00 sec)

mysql> SELECT `SC_categories`.* FROM `SC_categories` LEFT JOIN (SELECT DISTINCT(`parent`) FROM `SC_categories`) AS `list` ON (`SC_categories`.`category_ID` = `list`.`parent`) WHERE `list`.`parent` IS NULL;
+-------------+--------+
| category_ID | parent |
+-------------+--------+
|           3 |      2 |
|           5 |      4 |
|           7 |      6 |
+-------------+--------+
3 rows in set (0,00 sec)