MySQL: join many tables on one Statement

Question

i have the following DB-Structure / hierarchy:

TABLE product_type:

id,name, ....

TABLE product:

id,parent_id, name, ...

parent_id : is the product_type id

TABLE treeNode:

id, parent_id, name, type

its a tree hierarchy (roots have n sub Nodes) the number of levels of hierarchy is unknown.

the col type has the values "CATEGORY" OR "GROUP", that means, i have 2 trees:

Categories:

   TreeNode 1
         sub 1.1
                sub.1.1.1
                sub.1.1.2
                  ....
         sub 1.2
                sub.1.2.1
                sub.1.2.2
                  ....
   TreeNode 2
         sub 2.1
                sub.2.1.1
                sub.2.1.2
                  ....
         sub 2.2
                sub.2.2.1
                sub.2.2.2
                  ....

Groups:

   TreeNode 1
         sub 1.1
                sub.1.1.1
                sub.1.1.2
                  ....
         sub 1.2
                sub.1.2.1
                sub.1.2.2
                  ....
   TreeNode 2
         sub 2.1
                sub.2.1.1
                sub.2.1.2
                  ....
         sub 2.2
                sub.2.2.1
                sub.2.2.2
                  ....

TABLE linked_treeNode:

product_id, treeNode_id

lets say now, a user selects:

1: a product type (param: $selected_type)

2: a category (param: $selected_cat)

3: a group (param: $selected_group)

now i would like to display all Products which meets these selections:

1-) linked to selected Catagory or to its Subcategories

AND

2-) linked to selected Group or to its subGroups

AND

3-) linked to selected product type

what is the MySQL statement ( 1 Statement) for that?

i tried this:

SELECT P.* FROM 
product P, treeNode C, treeNode G, linked_TreeNode LC
WHERE 
p.parent_id='$selected_type' 
AND
( 
       C.type='CATEGORY' 
       AND 
       C.parent_id='$selected_cat' 
       AND 
       P.id=LC.product_id 
       AND 
       (LC.treeNode_id=C.id OR LC.treeNode_id='$selected_cat') 
)
AND
( 
       G.type='GROUP' 
       AND 
       G.parent_id='$selected_group' 
       AND 
       P.id=LC.product_id 
       AND 
       (LC.treeNode_id=G.id OR LC.treeNode_id='$selected_group') 
)
;

but i get allways 0 result!

i tried many other Statements(changes), with JOINS ..etc. but without success.

many thanks

EDIT: the Statement i used above is WRONG, so don't use it!

Try editing your question and putting in a query that uses `join`. — Gordon Linoff, Sep 03 '14 at 12:52
Is this a same question? - http://stackoverflow.com/questions/20215744/mysql-hierarchical-recursive-query — Kenan Zahirovic, Sep 03 '14 at 12:55
@KenanZahirovic, no, not exactly, i want the linked products to all selections, not the treenodes itself. and my TreeNodes have an additional col named type! — Rami.Q, Sep 03 '14 at 12:57
@Rami.Q Here are some suggestions for you in my answer, includes a join sample. Wish it helps. — Jaugar Chang, Sep 12 '14 at 17:11

score 1 · Accepted Answer · edited May 23 '17 at 11:59

How to get all descendants from a tree node with recursive query in MySql?

It's really a problem for MySql, and it's a key point to this question, but you still have some choices.

Assuming you have such sample data, not as much as your sample but enough to demonstrate:

create table treeNode(
id int, parent_id  int,  name varchar(10), type varchar(10),level int);
insert into treeNode 
(id, parent_id, name, type, level) values 
( 1,  0,  'C1    ', 'CATEGORY', 1),
( 2,  1,  'C1.1  ', 'CATEGORY', 2),
( 3,  2,  'C1.1.1', 'CATEGORY', 3),
( 4,  1,  'C1.2  ', 'CATEGORY', 2),
( 5,  4,  'C1.2.1', 'CATEGORY', 3),
( 3,  8,  'G1.1.1',    'GROUP', 3),
( 4,  9,  'G1.2  ',    'GROUP', 2),
( 5,  4,  'G1.2.1',    'GROUP', 3),
( 8,  9,  'G1.1  ',    'GROUP', 2),
( 9,  0,  'G1    ',    'GROUP', 1);

First choice: level code

Like the sample data of the name column in treeNode table. (I don't know how to say it in English, comment me about the correct expression of level code.)

To get all descendants of C1 or G1 could be simple like this:

select * from treeNode where type = 'CATEGORY' and name like 'C1%' ;
select * from treeNode where type = 'GROUP' and name like 'G1%' ;

I prefer this approach very much, even need us to generate these code before treeNode saved in application. It will be more efficient than recursive query or procedure when we have large number of records. I think this is a good denormalization approach.

With this approach, the statement you want with join could be:

SELECT distinct p.* --if there is only one tree node for a product, distinct is not needed
FROM product p
JOIN product_type pt
     ON pt.id= p.parent_id -- to get product type of a product
JOIN linked_TreeNode LC
     ON LC.product_id= p.id -- to get tree_nodes related to a product
JOIN (select * from treeNode where type = 'CATEGORY' and name like 'C1%' ) C --may replace C1% to concat('$selected_cat_name','%')
     ON LC.treeNode_id = C.id
JOIN (select * from treeNode where type = 'GROUP' and name like 'G1%' ) G --may replace G1% to concat('$selected_group_name','%')
     ON LC.treeNode_id = G.id
WHERE pt.name = '$selected_type'  -- filter selected product type, assuming using product.name, if using product.parent_id, can save one join by pt like your original sql

Sweet, isn't it?

Second choice: level number

Append a level column to treeNode table, as shown in the DDL.

Level number is much easier to maintain than level code in application.

With level number to get all descendants of C1 or G1 need a little trick like this:

SELECT id, parent_id, name, type, @pv:=concat(@pv,',',id) as link_ids 
  FROM (select * from treeNode where type = 'CATEGORY' order by level) as t
  JOIN (select @pv:='1')tmp
 WHERE find_in_set(parent_id,@pv)
    OR find_in_set(id,@pv);
 -- get all descendants of `C1`

SELECT id, parent_id, name, type, @pv:=concat(@pv,',',id) as link_ids 
  FROM (select * from treeNode where type = 'GROUP' order by level) as t
  JOIN (select @pv:=',9,')tmp
 WHERE find_in_set(parent_id,@pv)
    OR find_in_set(id,@pv) ;

This approach is slower than the first, but still faster than recursive query.

The full sql to the question omitted. Only need to replace those two subquery of C and G with two querys above.

Note:

There are many similar approach such as here , here, or even here. They won't work unless ordered by level number or level code. You could test the last query in this SqlFiddle by changing order by level to order by id to see the differences.

Another Choice: The Nested Set Model

Please reference to this blog, I did not test yet. But I think it's similar to last two choice.

It need you add a left number and a right number to the treenode table to enclose all descendants' ids between them.

thanks for your suggestions. i liked the "The Nested Set Model" but "as supposed" i have to modify my tree structure. — Rami.Q, Sep 23 '14 at 13:58

score 0 · Answer 2 · answered Sep 04 '14 at 14:56

This is not doable in MySQL as it is missing the feature that you need: Recursive querying.

Oracle can do this with a START WITH ... CONNECT BY statement.

You recurse over a table in a procedure and write the result in a temporary table. That table can be queried within the same session.

something like:

CREATE PROCEDURE products_by_cat_and_grp(typ INT, cat INT, grp INT)
BEGIN

-- create temporary table which we query later on
CREATE TEMPORARY TABLE tmp_products LIKE product;
ALTER TABLE tmp_products
ADD cat_id INT
, ADD grp_id INT;

-- first insert all products of the category and group
INSERT INTO  tmp_products
SELECT P.*, cat.id, grp.id
FROM linked_TreeNode lc 
JOIN product prd
  ON lc.product_id = prd.id
JOIN treeNode cat
  ON lc.treeNode_id = cat.id
JOIN treeNode grp
  ON lc.treeNode_id = grp.id
WHERE prd.parent_id = typ
  AND cat.id = cat
  AND grp.id = grp;

-- now we iterate over subcategories until there is nothing left
SET @rownum = 1;

WHILE @rownum > 0 DO
  CREATE TEMPORARY TABLE tmp_parents
  AS SELECT DISTINCT id, cat_id AS parent_id
  FROM tmp_products
  UNION SELECT DISTINCT id, grp_id AS parent_id
  FROM tmp_products;

  INSERT INTO  tmp_products
  SELECT P.*, cat.id, grp.id
  FROM linked_TreeNode lc 
  JOIN treeNode tn
  JOIN product prd
    ON lc.product_id = prd.id
  JOIN treeNode cat
    ON lc.treeNode_id = cat.id
  JOIN treeNode grp
    ON lc.treeNode_id = grp.id
  JOIN tmp_parents par
    ON (par.parent_id = cat.parent_id
    OR par.parent_id = grp.parent_id)
    AND par.id <> lc.product_id
  WHERE prd.parent_id = typ;
  -- see how many rows were inserted. If this becomes zero, the recursion is complete
  SET @rownum = ROW_COUNT();
END WHILE;

SELECT * FROM tmp_products;
END$$

This is not tuned or tested and i would not recommend it either as it can take a long time for the query to return.

MySQL: join many tables on one Statement

2 Answers2

How to get all descendants from a tree node with recursive query in MySql?

First choice: level code

Second choice: level number

Another Choice: The Nested Set Model

Linked