Table : categories
| id | name | parent |
|---|---|---|
| 1 | Electronics | null |
| 2 | TV | 1 |
| 3 | Portable | 1 |
| 4 | CRT | 2 |
| 5 | LCD | 2 |
| 6 | Plasma | 2 |
| 7 | Mp3 Players | 3 |
| 8 | CD Players | 3 |
| 9 | Flash | 7 |
Table : products (products are attached to the leaf category id)
| id | name | category_id |
|---|---|---|
| 1 | Flash Player | 9 |
| 2 | Samsung LCD TV | 5 |
| 3 | LG Plasma TV | 6 |
Query to generate the category_products results set ??
Results set :
| id | product_id | category_id |
|---|---|---|
| 1 | 1 (Flash Player) | 9 (Flash) |
| 2 | 1 (Flash Player) | 7 (Mp3 Players) |
| 3 | 1 (Flash Player) | 3 (Portable) |
| 4 | 1 (Flash Player) | 1 (Electronics) |
| 5 | 2 (Samsung LCD TV) | 5 (LCD) |
| 6 | 2 (Samsung LCD TV) | 2 (TV) |
| 7 | 2(Samsung LCD TV) | 1 (Electronics) |
| 5 | 3 (LG Plasma TV) | 5 (PLasma) |
| 6 | 3 (LG Plasma TV) | 2 (TV) |
| 7 | 3(LG Plasma TV) | 1 (Electronics) |
How can we generate this results set from mysql query ?
CodePudding user response：
WITH RECURSIVE
recursive_membership AS
(
SELECT
p.id AS product_id,
p.name AS product_name,
c.id AS category_id,
c.name AS category_name,
c.parent AS category_parent_id
FROM
products p
INNER JOIN
categories c
ON c.id = p.category_id
UNION ALL
SELECT
r.product_id,
r.product_name,
c.id,
c.name,
c.parent
FROM
recursive_membership r
INNER JOIN
categories c
ON c.id = r.category_parent_id
)
SELECT
product_id,
product_name,
category_id,
category_name
FROM
recursive_membership