select parent.menu_label, child.menu_label from menu parent left join menu child on
child.menu_parent = parent.menu_id;

might add "where menu_id != 0" [as a guess]
might use an inner join if you want all children, but no parents that have no children ...

also, menus like this are easier to display if you make sure the output is sorted first by the
parent.menu_label, then by child.menu_label (optional) ... so you can do control-break processing
on the output, and show the parent menu label only once for each group of children. with the left
join, you'll get labels even for menus that have no children.

with a subselect:
select (select parent.menu_label from menu parent where parent.menu_id = child.menu_parent where
parent.menu_id != 0), child.menu_label from menu child;

note that neither of these is recursive -- if you need your menu to handle N levels ... you'll need
to do it differently. if that's the case, ask again.

if this doesn't answer your question ... ask again.

-philip

> Hello guys, i have a table that references to it self, it looks like:
> menu_id, menu_label, menu_parent.
>
> If parent is diferent from zero, i want to show the menu_label of the parent
> one. Does anyone know how to do it? I've tryied with sub-selects but with no
> success.
>
> Thanx