Getting root node for a given leafs IDs

Question

I got the following tables:

  +-----------------+
  | PRODUCTS        |
  +-----------------+
  | #product_id     |
  | #company_id     |
  +-----------------+
        |     |
+---------------------+
| PRODUCT_BUNDLES     |
+---------------------+
| #parent_product_id  |
| #child_product_id   |
| #company_id         |
+---------------------+

And here is a result in a tree like of the relationships among ids in the PRODUCT_BUNDLES table for some product:

A user give me the following ids p2, p4, p5 and p6 (the leafs of the tree) and I want return the p1 id (the root of the tree).

How I build select query in mysql to do that?

For now I only have the inverse query. From a root ID give the descendent's IDs. `SELECT bundles.product_id FROM products_bundles bom INNER JOIN products products ON products.product_id = bom.parent_product_id INNER JOIN products bundles ON bundles.product_id = bom.child_product_id WHERE products.product_id = SOMEID;` — mcanaves, Sep 10 '14 at 11:33
Have a look at the solution in here http://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql — Mikpa, Sep 10 '14 at 11:42
The suggested solution is what I have for now but more simple :). Given parent ID get the descendants. What I search is a query that return the parent who have all product_id given as descendant. Maybe the suggested solution is a good way to proceed but I am unable to see the way. — mcanaves, Sep 10 '14 at 12:15
Finally get the solution from http://dba.stackexchange.com/questions/7147/find-highest-level-of-a-hierarchical-field-with-vs-without-ctes using `GetAncestry`procedure — mcanaves, Sep 11 '14 at 15:29

score 0 · Answer 1 · edited May 23 '17 at 12:17

You can create PROCEDURE like this one

 CREATE PROCEDURE get_root(IN id int)
  BEGIN
  DECLARE child_id int;
  DECLARE prev_id int;
  SET prev_id = id;
  SET child_id= -1 ;
  SELECT parent_product_id into child_id 
  FROM PRODUCT_BUNDLES WHERE child_product_id= prev_id ;
  WHILE child_id <> -1 DO
    SET prev_id = child_id;
    SET child_id= -1 ;
    SELECT parent_product_id into child_id 
    FROM PRODUCT_BUNDLES WHERE child_product_id = prev_id ;
  END WHILE;
  SELECT child_id ;
  END

Getting root node for a given leafs IDs

1 Answers1