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)
