Mysql - Recursive query to get only parent categories

Question

i have the following table category:

id | desc_cat       | parent_id
19 | Pessoais       | (null)
20 | Cartao Credito | 19
21 | Academia       | 19
22 | Residencia     | (null)
23 | Agua           | 22
24 | Luz            | 22
25 | Aluguel        | 22

And I’ve got the table containing launches with a name cashbook:

id  | value    | category_id
177 | 480.55   | 20
178 | 100.00   | 24
179 | 580.00   | 25
180 | 80.00    | 21
181 | 28.00    | 23

I need to make an appointment at cashbook and return the values summed and grouped by the parent category (where it is as null), for example:

Categoria-Pai | Total
Pessoais      | 560,55
Residencia    | 708

Can you help me assemble this query?? I picked up some examples of recursive queries but I’m very lost. My system is being made in PHP (Yii framework).

Answer 1

Thanks guys, the SQL below solved my problem:

SELECT y.desc_category as segment, sum( x.value) as total FROM (
SELECT category.id_category, category.desc_category, category.parent_id , c.value AS value
FROM category
INNER JOIN cashbook AS c ON category.id_category = c.category_id )AS x 
INNER JOIN category AS y ON x.parent_id = y.id_category
INNER JOIN user AS u ON y.user_id = u.id
WHERE u.id = 3
GROUP BY y.desc_category

Answer 2

gugoan, as I said in the comment, if Mysql would support CTE, the query would be much simpler, in any case I would like that in your next question about SQL, you prepare a Sqlfiddle like this: http://sqlfiddle.com/#! 9/add59/1

Node that its categories were a tree with only two levels, so all eyes are directly linked to the root, in this case a simple LEFT JOIN with COALESCE should work.

SELECT
  id,
  desc_cat,
  SUM(value)
FROM (
  SELECT 
    COALESCE(CatParent.id, Category.id) id, 
    COALESCE(CatParent.desc_cat, Category.desc_cat) desc_cat,
    CashBook.value
  FROM CashBook
  JOIN Category ON CashBook.category_id = Category.id
  LEFT JOIN Category AS CatParent ON Category.parent_id = CatParent.id
) AS CatParent
GROUP BY id, desc_cat

But this approach will only work in this situation, if your tree goes deeper, you will have problems... for example... if your table Category has the following data:

| id |            desc_cat | parent_id |
|----|---------------------|-----------|
| 26 |         Categoria 1 |    (null) |
| 27 |   Sub Categoria 1.1 |        26 |
| 28 | Sub Categoria 1.1.1 |        27 |
| 29 | Sub Categoria 1.1.2 |        27 |
| 30 |   Sub Categoria 1.2 |        26 |
| 31 | Sub Categoria 1.2.1 |        30 |
| 32 | Sub Categoria 1.2.2 |        30 |

I even tried to emulate a recursive query using CTE using View or a Function with MySQL... but I couldn’t do it in the SQLFiddle, I believe that the MySQL does not support this type of structure.

You can still use one WHILE to try to do something but its MySQL you will not be able to infer an execution plan, your query will become complex. I would only advise you to do this for a scheduled JOB.

However as this data will be consumed in PHP, the ideal is to do this type of direct control in PHP... unfortunately I do not know enough PHP to give an example, but I can update this answer with a snippet of Code in C# or Javascript, then you could make a port from this logica to PHP.