I have two tables:

// tags
+----+-----------------------+-----------+
| id |         name          | parent_id |
+----+-----------------------+-----------+
| 1  | Home                  | Null      |
| 2  | Kitchen               | 1         |
| 3  | Serving and reception | 2         |
| 4  | Spoon                 | 3         |
| 5  | Digital               | NULL      |
| 6  | Communication         | 5         |
| 7  | Cellphone             | 6         |
+----+-----------------------+-----------+

// products
+----+------------------------------------------+--------+
| id |                name                      | tag_id | -- this is the deepest tag id
+----+------------------------------------------+--------+
| 1  | Dinner Spoon Set,16 Pcs 7.3" Tablespoons | 4      | 
| 2  | iPhone 14 Promax                         | 7      |
| 3  | Samsung A20                              | 7      |
+----+------------------------------------------+--------+

I need to implement a products listing page per “tag”. So, I need to get

| 2  | iPhone 14 Promax                         | 7      |
| 3  | Samsung A20                              | 7      |

by passing each of the following tags.ids: 5, 6, 7 (since they are all related to that products)

Any idea how can I do that?

Real data simplified fiddle

Here is my current query: (the problem is it returns just one product, not a list of products)

WITH RECURSIVE cte (id, name, parent_id, orig_id) AS (
    SELECT id, name, parent_id, id AS orig_id
    FROM tags
    WHERE parent_id IS NULL
    UNION ALL
    SELECT t1.id, t1.name, t1.parent_id, t2.orig_id
    FROM tags t1
    INNER JOIN cte t2
        ON t2.id = t1.parent_id
)

SELECT MAX(p.name) AS name
FROM cte t
LEFT JOIN products p
    ON p.tag_id = t.id
GROUP BY t.orig_id
HAVING SUM(t.id = 6) > 0;