How to display parent categories and subcategories on the company page?

Question

MySQL

Guys, hello!
I ran into a problem, I can not get the entire list of categories and subcategories that relate directly to the company.
The structure of the database is as follows:
categories - category table
category_id - int
category_title - varchar (150)
parent - int (if it is a child category, then the parent ID is written, otherwise it is 0)
company_categories - a table in which the categories related to the company are written
category_id - int
company_id - int
companies - table with company data
id - int
name - varchar(150)
etc.
The bottom line is that when entering the company page, the URL contains the company ID, I need to pull out all the categories to which this company belongs, as well as their parents, to get the following view:
Category
-Subcategory
--Subcategory
One more category
-More one subcategory
--Another subcategory
Third category
------------------------------------------------------
Tried to do next query:

SELECT
`cat`.`category_id`,
`cat`.`category_title`
FROM
`company_categories` AS `comp_cat`
JOIN
`companies` AS `company`
JOIN
`categories` AS `cat`
WHERE
`comp_cat`.`category_id`=`cat`.`category_id`
AND `comp_cat`.`company_id`=`company`.`company_id`
AND `company`.`company_id`=1

but this query only returns the child categories that the company belongs to.

Answer 1

If you specify only the category of the last level in company_categories, then a regular query will not be able to pull it out. You can make a recursive query using a stored procedure, example .