MySQL: joining many tables into one statement
I have the following database structure / hierarchy:
TABLE product_type :
id,name, ....
TABLE product :
id,parent_id, name, ...
parent_id : product_type id
TreeNode TABLE :
id, parent_id, name, type
its hierarchy is a tree (roots have sub n nodes) the number of levels of the hierarchy is unknown .
col type
is "CATEGORY" OR "GROUP" , this means I have 2 trees:
Category:
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
now let's say the user selects:
1: product type (param: $selected_type
)
2: category (param: $selected_cat
)
3: a group of (param: $selected_group
)
now I would like to display all products that match these selections:
1-) associated with the selected Catagory or its subcategories
AND
2-) associated with the selected group or its subgroups
and
3-) associated with the selected product type
what is MySQL (1 expression) for this?
I've 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 all 0 results
I tried many other assertions (changes) with JOINS..etc. but unsuccessfully.
many thanks
EDIT: The expression I used above is WRONG, so don't use it!
source to share
How to get all descendants from a node tree with recursive query in MySql?
This is indeed a problem for MySql and is the key point for this question, but you still have a choice.
Assuming you have example data like this, 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 for the name column in the treeNode table. (I don't know how to say this in English, please comment on the correct expression for me level code
.)
To get all descendants C1
or G1
maybe simple:
select * from treeNode where type = 'CATEGORY' and name like 'C1%' ;
select * from treeNode where type = 'GROUP' and name like 'G1%' ;
I really prefer this approach, even we need to generate this code before the treeNode is saved in the application. This will be more efficient than a recursive query or procedure when we have a large number of records. I think this is a good denormalization approach.
With this approach, the assertion you want with attachment 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
Add a level column to the treeNode table as shown in the DDL.
The level of the level is much easier to maintain than the code of the level in the application.
With a level number so all descendants C1
or G1
need a little trick:
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 the recursive query.
Full sql to the question is omitted. You just need to replace these two subqueries C and G with the two queries above.
Note:
There are many similar approaches, such as here , here, or even. They will not work unless a level number or level code is ordered. You can check the last query in this by changing to to see the differences. SqlFiddleorder by level
order by id
Another choice: nested set model
Please refer to this blog I have not tested yet. But I think it looks like the last choice.
You need to add the left number and the right number to the treenode table to wrap all descendant IDs between them.
source to share
This is not feasible in MySQL because a required feature is missing: a recursive query.
Oracle can do this with the START WITH ... CONNECT BY statement.
You process the table in a procedure and write the result to a temporary table. This table can be queried in one 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 configured or tested and I would not recommend it as it can take a long time to request.
source to share