Chandrakar Ramkishan Chandrakar Ramkishan - 1 month ago 5
MySQL Question

How to select all the sub_catagory records with its main_catagory_name and parent_id in a single query in MySql?

My catagory table is look like as folows :

---------------------------------------
id | name | parent_id |

1 | Vehicles | 0 |
2 | Car Insurance | 1 |
3 | Van Insurance | 1 |
4 | PhoneRecharge | 0 |
5 | prepaid | 4 |
6 | postpaid | 4 |


Output should be look like as folows :

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


Here 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_catagory_name FROM catagory tab1 inner join catagory tab2 on tab1.parent_id = tab2.id

Here is the query for your table.

Comments