How do I find Main Category name with multiple joins?

Question

I have two tables

Categories

ID  PARENT_ID  NAME
1     0        Shopping
2     1        Health

Business_Categories

ID  BUSINESS_ID  CATEGORY_ID  
1    1                1
2    1                2

Now I want below mentioned details

BUSINESS_ID SUBCATEGORY_NAME MAINCATEGORY_NAME

But I don't know how could I do it?

Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Raymond Nijland, Jul 18 '19 at 12:00
That duplication has answers from old versions to the new MySQL 8 version... — Raymond Nijland, Jul 18 '19 at 12:00
Possible duplicate of [Mysql JOIN (multiple) tables](https://stackoverflow.com/questions/14864933/mysql-join-multiple-tables) — Dmytro Chasovskyi, Jul 18 '19 at 12:05

Cajun · Accepted Answer · 2019-07-18T12:29:25.220

1

select
bus.business_id, cat2.name as subcategory_name, cat1.name as maincategory_name
from 
business_categories as bus
inner join categories as cat1 on bus.category_id = cat1.id
inner join categories as cat2 on cat1.parent_id = cat2.id

edited Jul 18 '19 at 12:29

answered Jul 18 '19 at 12:02

Cajun

36
1
6

It returns empty result set – Jaydeep Rajgor Jul 18 '19 at 12:05

How do I find Main Category name with multiple joins?

1 Answers1