I want to find all products, where top root category is active. So I have created stored procedure WITH_EMULATOR based on that article WITH RECURSIVE and MySQL to get root category. Default query:
SELECT * FROM shop_products
INNER JOIN shop_category ON (shop_products.category_id=shop_category.id)
WHERE shop_products.active=1
AND (SELECT sc.active FROM shop_category as sc WHERE sc.id = shop_category.parent_id) = 1
And then I tried to do it in one query:
SELECT * FROM shop_products
INNER JOIN shop_category ON (shop_products.category_id=shop_category.id)
WHERE shop_products.active=1
AND (CALL WITH_EMULATOR(
"EMPLOYEES_EXTENDED",
"
SELECT ID, PARENT_ID, ACTIVE
FROM shop_category as sc
WHERE sc.ID = shop_category.parent_id
",
"
SELECT P.ID, P.PARENT_ID, P.ACTIVE
FROM shop_category AS P INNER JOIN EMPLOYEES_EXTENDED C ON C.PARENT_ID = P.ID
",
"
SELECT ACTIVE
FROM EMPLOYEES_EXTENDED
WHERE PARENT_ID = 0
ORDER BY id
",
0,
""
)) = 1
But I got an error:
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CALL WITH_EMULATOR(
"EMPLOYEES_EXTENDED",
"
SELECT ID, PARENT_ID, ACTIVE
' at line 4
What am I doing wrong? Thanks
shop_category:
id | parent_id | name | active
1 NULL Literature 1
2 1 Interesting books 1
3 2 Horrible books 1
4 1 Books to burn 1
5 NULL Motorized vehicles 0
6 5 Cars 1
7 6 Motorbikes 1
shop_products:
id | category_id | name
1 7 Cooking for dummies
2 7 Twilight saga
3 7 My grandpa's car
According to this example I should get nothing, because category with id 7 has root parent active = 0.
