MySQL return all records in a hierarchical struture -

i've got simple hierarchical table 'groups' store parent-id of each child.

i'm trying query each member of table groups following query:

select groups.name  'group name',         groups1.name 'group1 name'    groups         left join groups groups1                on groups.id = groups1.parent_id   groups.parent_id = 0  order  groups.id,            groups1.id  

and i'm getting:

|group name......................................|group1 name............|
|--------------------------------------------------------------------------|
|assets...............................................|fixed assets..............|
|assets...............................................|current assets..........|
|assets...............................................|investments...............|
|liabilities , owners equity............|capital account.........|
|liabilities , owners equity............|current liabilities......|
|incomes............................................|direct incomes...........|

but want line each node if there child i.e.:

|group name......................................|group1 name............|
|--------------------------------------------------------------------------|
|assets...............................................|..................................|
|assets...............................................|fixed assets..............|
|assets...............................................|current assets..........|
|assets...............................................|investments...............|
|liabilities , owners equity............|....................................|
|liabilities , owners equity............|capital account.........|
|liabilities , owners equity............|current liabilities......|
|incomes............................................|..................................|
|incomes............................................|direct incomes...........|

is there way query or queries?

instead of setting parent_id 0 top-level entry, set parent_id (that is, parent_id = id). join top-level "children" include itself.

select parent.name `group name`,    if(parent.id = child.id, '', child.name) `group1 name`  groups parent join groups child    on ((parent.id = child.parent_id)))  parent.parent_id = parent.id  order parent.id, child.id 

note don't need left outer join, because there's guaranteed @ least 1 "child".

you use closure table design have described in several posts on stack overflow:

• what efficient/elegant way parse flat table tree?