relationships - MYSQL Getting Main Categories -

i have following query lists categories , parent category related to.

t.term_id category id tx.parent id of category depend from.  main categories have parent id= 0 

and problem since query not displaying main categories sub-category. showing records parent>0 need parent =>0. put in condition doesn't work.

select t.term_id, t.name, tx.parent, tm.name mg_terms t  join mg_term_taxonomy tx on tx.term_id = t.term_id  join mg_terms tm on tm.term_id = tx.parent  tx.taxonomy='product_cat'   order `tx`.`parent`  desc 

any main categories (parent=0) well?

you can either make use of union select , join 2 results:

select t.term_id, t.name, tx.parent, tm.name mg_terms t join mg_term_taxonomy tx on tx.term_id = t.term_id join mg_terms tm on tm.term_id = tx.parent tx.taxonomy='product_cat'   union  select t2.term_id, t2.name, tx2.parent, null mg_terms t2 join mg_term_taxonomy tx2 on tx2.term_id = t2.term_id tx2.taxonomy='product_cat' , t2.parent = 0 order parent 

or select mg_terms t , left join mg_terms tm, suggested sagi.

select t.term_id, t.name, tx.parent, tm.name mg_terms t join mg_term_taxonomy tx on tx.term_id = t.term_id left join mg_terms tm on tm.term_id = tx.parent tx.taxonomy='product_cat'   order `tx`.`parent`