mysql, join on same table?

I have a table that looks like

id, parentId, name

1, null, first

2, null, second

3, 1, child of first

I want to do a query so that I can end with with rows that looks like

1开发者_JAVA技巧, null, first

2, null, second

3, first, child of first

so basically something like

SELECT id, (SELECT name FROM pages WHERE id=parentId), name FROM pages

of course obviously that query is horrible.

You could try something like the following, it is restricted to two levels Parent and child. It wont work for 3 or more levels eg. Parent->Child->Child

SELECT  Parent.ID,
        Child.name,
        Parent.Name
FRom    yourTable as Parent LEFT JOIN 
        yourTable as Child On Parent.ParentID = Child.ID

Try:

select id, parent = null, name from pages where parentid is null

union all

select id, parent = top.name, name = sub.name
from pages sub
     join pages top on sub.parentid = top.id