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)
;