Q:

Select category list have parent category name?

My problem is how to get category list to show in table having parent name ?
Category table structure like that:

Category data :
ID  vn_name   en_name   parent_id   path
-----------------------------------------------
1   Sanpham   Product   0            0-1-
2   Apple     Apple     1            0-1-2-
3   Sony      Sony      1            0-1-3-
4   Iphone    Iphone    2            0-1-2-4-
5   Ipad      Ipad      2            0-1-2-5-
6   Xperia    Xperia    3            0-1-3-6-
...

So, how to query to get list like that ?
ID  vn_name   en_name   vn_parent     en_parent      
-----------------------------------------------------
1   Sanpham   Product   Root            Root         
2   Apple     Apple     Sanpham         Product      
3   Sony      Sony      Sanpham         Product
4   Iphone    Iphone    Apple           Apple
5   Ipad      Ipad      Apple           Apple
6   Xperia    Xperia    Sony            Sony
...

A:

You can achieve this by joining table itself using LEFT JOIN.
Try out this:
select c1.id,
       c1.vn_name,
       c1.en_name,
        -- if parent id is 0 then make it root
       if(c1.parent_id =0,'root',c2.vn_name) as vn_parent,
       if(c1.parent_id =0,'root',c2.en_name) as en_name
from Category c1 left join Category c2 on c1.parent_id=c2.id
order by c1.id

SQL Fiddle Example: http://sqlfiddle.com/#!9/b6a63/3

