Can anyone tell me the table from where I will get subcategory name. I want to fire query using table.
Magento category is an EAV entity. So you cannot retrieve the information you wanted from a single table.
Explaining EAV concept is out of scope with regards to this question. However you can use Wiki document for a good start. So in short, An EAV entity is stored using multiple tables and to retrieve a "valid data" you need to join multiple tables.
Since magento database uses EAV ( Entity, Attribute, Value ) structure to manage everything including sub-categories that you are looking for. The list of Categories & Subcategories can be found on
Note : Simple sql query is not a best magento way of obtaining category and subcategories.
For geting categories name you can also use catalog/category collection.
//Load catalog category model collection <?php $categories = Mage::getModel('catalog/category')->getCollection();?> <?php foreach ($categories as $category): ?> <?php echo '<pre>';?> <?php print_r ($category->getData('name')); ?> <?php endforeach; ?>
The above code will print all categories name.
For detail refer this blog : http://blog.chapagain.com.np/magento-get-list-of-all-categories/