mysql, join on same table?

Question

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, 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.

Answer 1

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

Answer 2

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