How to select all sub_category entries with its main_category_name and parent_id in a single query in MySQL?

Question

My category table is as follows:

------------------------------------ id | name | parent_id | ------------------------------------ 1 | Vehicles | 0 | 2 | Car Insurance | 1 | 3 | Van Insurance | 1 | 4 | PhoneRecharge | 0 | 5 | prepaid | 4 | 6 | postpaid | 4 | 

The result should look like this:

 --------------------------------------------------------- id | parent_id | main_category_name | sub_category_name| --------------------------------------------------------- 2 | 1 | Vehicles | Car Insurance | 3 | 1 | Vehicles | Van Insurance | 5 | 4 | PhoneRecharge | prepaid | 6 | 4 | PhoneRecharge | postpaid | 

To get the above record, I need to minimize the interaction with the database. Therefore, I need to achieve this above data in one query.

Answer 1

Here you will find: http://sqlfiddle.com/#!9/1b1a7a/14

SQL: SELECT * FROM category t1 INNER JOIN (SELECT * FROM category WHERE parent_id != 0) t2 ON t1.id = t2.parent_id

Answer 2

This should work:

 SELECT c.id AS cat_id, parent.id AS parent_id, parent.name AS main_category_name, c.name AS sub_category_name FROM category c JOIN category parent ON c.parent_id = parent.id