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.

@HimanshuAhuja, Here I want to get all parent and child products from product table for a particular parent category id. Eg: assume I have `cid=2` then I need `p1,p2,p3,p4,p5,p6` from product table — user3733831, Dec 10 '19 at 03:38
Its not really obvious from the table definations what is a parent or child and what fields between the two tables relate. e.g. is cid a category id or a child id? An example that includes all outputs doesn't clarify it that much. — danblack, Dec 10 '19 at 04:42
what's a parent? What's a child? Are these totally within category. Some more examples would help. A broken SQL example doesn't clarify much. Are you expecting parents of parents? and children of children? If so MySQL-8.0 or MariaDB-10.2+ is needed for recursive CTEs to get your answer. — danblack, Dec 10 '19 at 05:22
@danblack exactly my point. How come cid 2 in your above data has all products. Post relevant data not explanation. Relevant data is self explanatory at times — Himanshu, Dec 10 '19 at 05:36

Nick · Accepted Answer · 2019-12-10T05:56:20.150

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

edited Dec 10 '19 at 05:56

answered Dec 10 '19 at 05:32

Nick

138,499
22
57
95

great mate u understood what the OP meant by the above query and data. I wonder if Ill be able to catch the same. – Himanshu Dec 10 '19 at 05:39
@Nick This is actually what I needed. Thank you very much. One question how I get category name along with this select? – user3733831 Dec 10 '19 at 05:52
I tried it changing outer query as this `SELECT p.pid ,p.pname ,c.name` but its not working – user3733831 Dec 10 '19 at 05:54
@user3733831 please see my edit. I've added a query which will fetch the category name as well – Nick Dec 10 '19 at 05:56
Great. one more question... can we fetch specific columns instead of `*` in outer query? – user3733831 Dec 10 '19 at 06:01
@user3733831 of course. For example, you can just select `c.name` and `p.pname` if that's all you're interested in. I just chose `*` to show all the information that was available from the query. – Nick Dec 10 '19 at 06:02
Yes I tried it as `SELECT p.cid, p.pname,c.name` but there is an error `Query Error: Error: ER_BAD_FIELD_ERROR: Unknown column 'c.name' in 'field list'` – user3733831 Dec 10 '19 at 06:04
Sorry.. it seems its my fault. Now its working perfectly. Thank you very much – user3733831 Dec 10 '19 at 06:06
@user3733831 I think you didn't use my updated query, it works fine for me too https://www.db-fiddle.com/f/hpheuKtHJ5U2445GrKozke/3 – Nick Dec 10 '19 at 06:07
Sorry @Nick, I have one more question. I need to get parent category name along with others – user3733831 Dec 10 '19 at 08:26
Sheesh! You're making me work hard! :-) try this: https://www.db-fiddle.com/f/hpheuKtHJ5U2445GrKozke/5 – Nick Dec 10 '19 at 09:40

score 0 · Answer 2 · edited Dec 10 '19 at 04:34

0

select product.pname, product.cid, category.parent
  from product 
 inner join category
    on product.cid = catgeory.cid;

edited Dec 10 '19 at 04:34

danblack

12,130
2
22
41

answered Dec 10 '19 at 04:08

Mamta Soni

1
1

This is not what I needed – user3733831 Dec 10 '19 at 04:55
Don't worry @mamta-soni , I couldn't work out the question either. Still can't. – danblack Dec 10 '19 at 05:00
@danblack I updated question with DB model. pls check it – user3733831 Dec 10 '19 at 05:12

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

2 Answers2