Use output of one statement as input for another (recursively)
Solution 1:
Here's an example of using Recursive CTE to solve the problem. This will handle practically any number of category levels.
There are a few ways to do this.
Your test data had some mistakes. I've corrected them for this test.
Note: There was an outer join, because your initial data didn't have a matching category related to Chips. That can be removed if there is no missing data. I'm going to remove it now.
The fiddle
WITH RECURSIVE cte1 (id, name, cid, cname, level, parent_id) AS (
SELECT p.id, p.product_name, c.id, c.name, level, parent_id
FROM product AS p
JOIN categories AS c
ON p.ext_category_id = c.ext_id
UNION ALL
SELECT p.id, p.name, c.id AS cid, c.name AS cname, c.level, c.parent_id
FROM cte1 AS p
JOIN categories AS c
ON c.id = p.parent_id
)
SELECT id, name, cname
FROM cte1
WHERE level = 1
ORDER BY id
;
The result:
+------+--------------+------------------------------+
| id | name | cname |
+------+--------------+------------------------------+
| 1 | Strawberries | Fruit & Vegetable department |
| 2 | Bananas | Fruit & Vegetable department |
| 3 | Potatoes | Fruit & Vegetable department |
| 4 | Car Battery | Automotive department |
| 5 | Chips | Junk Food Department |
+------+--------------+------------------------------+
The setup:
CREATE TABLE product (
id int
, product_name varchar(40)
, ext_category_id int
);
INSERT INTO product VALUES
( 1, 'Strawberries' , 101)
, ( 2, 'Bananas' , 102)
, ( 3, 'Potatoes' , 103)
, ( 4, 'Car Battery' , 104)
, ( 5, 'Chips' , 105)
;
CREATE TABLE categories (
id int
, ext_id int
, name varchar(40)
, level int
, parent_id int
);
-- id ext_id name level parent_id
INSERT INTO categories VALUES
(1001, 101, 'Fruit' , 2, 2000)
, (1002, 102, 'Fruit' , 2, 2000)
, (1003, 103, 'Vegetables' , 2, 2000)
, (1004, 104, 'Car Parts' , 3, 2500)
, (1005, 105, 'FoodParts' , 2, 2001)
, (2001, 209, 'Junk Food Department' , 1, Null)
, (2000, 205, 'Fruit & Vegetable department', 1, Null)
, (2500, 309, 'Cars & Trucks' , 2, 2002)
, (2002, 209, 'Automotive department' , 1, Null)
;