Self Join Question

[djBuilder]

Does anyone know how to do a self join farther than one level deep? i.e. I have a category table, and the category has a label, an id and a parentId.

SELECT a.label AS parent_label, b.label AS child_label FROM category AS a, category AS b WHERE a.id = b.parentId;

And that is fine if the child does not have any children. But if I have autos > seats > leather

When I run the query I get:

1 Auto Seats
2 Seats Leather

I have written a recursive function that will follow it all the way down, but I'm curious if a self join can do it without php.

Ideally, I would like to say something like,
please choose a category:

Autos ::
Autos :: Seats
Autos :: Seats :: Leather

Thanks for any ideas!

[nnichols]

SELECT a.label AS parent_label,
b.label AS child_label_1,
c.label AS child_label_2,
d.label AS child_label_3
FROM category AS a 
LEFT JOIN category AS b
ON a.id = b.parentId
LEFT JOIN category AS c
ON b.id = c.parentId
LEFT JOIN category AS d
ON c.id = d.parentId
WHERE a.parentId IS NULL;[CODE]
Hope this helps ;)