Chandrakar Ramkishan Chandrakar Ramkishan - 27 days ago 5
MySQL Question

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

My category table is looking like the following:

------------------------------------
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 output should look like the following:

---------------------------------------------------------
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 my database interaction. So I need to achieve this above data in a single query.

Answer
SELECT
  tab1.id,
  tab2.id as parent_id,
  tab2.name as main_category_name,
  tab1.name as sub_category_name
FROM
  category tab1 inner join category tab2 on tab1.parent_id = tab2.id

Here is the query for your table.

Comments