How to create a MySQL hierarchical recursive join

Question

I have a problem getting markup_category value.

I have to calculate product retail prices.

retail_price = price + (price / 100 * markup_category)

So when product category has markup_category it is not problem. But sometimes category does not have this value and then parent category markup must be used. I can not take its value in one query.

Table structure:

category_id	parent_id	markup_category
168	0	50.00
1048	168	0.00
1092	1048	0.00

And when I try to do something like: (took query here: How do I import an SQL file using the command line in MySQL?)

SELECT category_id, @pv:=parent_id AS parent_id, markup_category FROM cscart_categories JOIN (SELECT @pv:=1092) tmp WHERE category_id=@pv

I get only this as result:

category_id	parent_id	markup_category
1048	168	0.00
1092	1048	0.00

How to get first category with markup_category > 0 ?

Answer 1

This helped me in this case

WITH RECURSIVE markup_categories AS (
    SELECT 
        category_id, 
        parent_id, 
        markup_category 
    FROM 
        cscart_categories 
    WHERE 
        category_id = 1092 
    UNION ALL 
    SELECT 
        c2.category_id, 
        c2.parent_id, 
        c2.markup_category 
    FROM 
        cscart_categories AS c2, 
        markup_categories AS mc 
    WHERE 
        c2.category_id = mc.parent_id
) 
SELECT 
    * 
FROM 
    markup_categories