Mysql how to get parent and all child for given ID?

Question

I have two table (category, product) as below:

Category Table:

cid name parent
1  items Null
2    A    1
3    aa   2
4    ab   2
5    ac   2
6    B    1
7    ba   5
8    bb   5
9    bc   5
10   C    1
11   ca   9
12   cb   9
13   cc   9

Product Table:

pid  cid  pname
1     2   p1
2     3   p2
3     4   p3
4     4   p4
5     5   p5
6     5   p6

Here I want to get all parent and child products for a particular parent. Here I have cid = 2, then I need all the products to cid=2 and its child.

Here I tried it something like this, but not sure how to join product table with this query:

select  cid,
        name,
        parent
from    (select * from categories
         order by parent, cid) categories,
        (select @pv := '2') initialisation
where   find_in_set(parent, @pv) > 0
and     @pv := concat(@pv, ',', cid)

UPDATES: DB Model

Can anybody help me out? Thank you.

Answer 1

First it should be noted that your query won't give you the root category, only its children. So you need to add a UNION to it to include the root category. Secondly you don't need a subquery for the recursive part, you can do the ordering in the query directly. You can then use that query as a derived table and JOIN it to the products table:

SELECT *
FROM (
      SELECT 2 AS cid
      UNION ALL
      (SELECT  cid
       FROM categories
       CROSS JOIN (SELECT @pv := '2') initialisation
       WHERE   find_in_set(parent, @pv) > 0
         AND   @pv := concat(@pv, ',', cid)
       ORDER BY parent, cid)
      ) c
JOIN products p ON p.cid = c.cid

Output (for your sample data)

cid     pid     cid     pname
2       1       2       p1
3       2       3       p2
4       3       4       p3
4       4       4       p4
5       5       5       p5
5       6       5       p6

You might want just a list of product names, in which case you can replace * in the outer query with e.g. GROUP_CONCAT(p.pname) AS products and get the result:

products
p1,p2,p3,p4,p5,p6

Demo on dbfiddle

To also get the category name, you must SELECT that in the inner query:

SELECT *
FROM (
      SELECT cid, name
      FROM categories
      WHERE cid = 2
      UNION ALL
      (SELECT  cid, name
       FROM categories
       CROSS JOIN (SELECT @pv := '2') initialisation
       WHERE   find_in_set(parent, @pv) > 0
         AND   @pv := concat(@pv, ',', cid)
       ORDER BY parent, cid)
      ) c
JOIN products p ON p.cid = c.cid

Output:

cid     name    pid     cid     pname
2       A       1       2       p1
3       aa      2       3       p2
4       ab      3       4       p3
4       ab      4       4       p4
5       ac      5       5       p5
5       ac      6       5       p6

Demo on dbfiddle