join query in same table with condition

I have inserted both category and sub-category in same table.

id , name , mainCat ,

Here is the name of the category .If a user add a subcategory then i will add the parent cate开发者_JAVA技巧gory to maincat and sub category to the name.

but when come sorting i have sort Category wise.

can any one suggest me best query

As long as you only have two levels of categories then a query like the following using a self-join will suffice. However, if you have many levels (i.e. a hierarchy) then the problem becomes a lot harder. See http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ for more information.

SELECT p.Name, s.Name
FROM Categories s
LEFT JOIN Categories p ON s.mainCat = p.ID
ORDER BY p.Name, s.Name;

i tried with query a lot but finally i made it with php

$category = mysql_query("SELECT * FROM category WHERE mainCat = '' ORDER BY `name` ASC");

while($data = mysql_fetch_array($category)){
    $array[] = $data;
    $subCategory = mysql_query("SELECT * FROM category WHERE mainCat ='".$data['name']."' ORDER BY `name` ASC");
    echo "SELECT * FROM category WHERE mainCat ='".$data['name']."' ORDER BY `name` ASC".'<br/>';
    while($data2 = mysql_fetch_array($subCategory)){
        if($data2['mainCat']!=""){
            $array[] = $data2;
        }
    }   
}

have u try below:

SELECT t1.id, t1.name
FROM tablename t1
LEFT JOIN tableName t2
ON t2.name = t1.mainCat