MYSQL+PHP, How to add items from different tables SUM()?

Question

I have two tables:

table_entries

| id | descricao                 | quantidade |
| 1  | Maquina Prensa            | 1          |
| 2  | Maquina Secadora          | 2          |

table_bom_entries

| id | id_itens | codigo | quantidade |
| 1  | 1        | mp001  | 1          |
| 2  | 1        | mp002  | 2          |
| 3  | 1        | fx001  | 1          |
| 4  | 1        | fx002  | 5          |
| 5  | 2        | ms001  | 1          |
| 6  | 2        | ms002  | 2          |
| 7  | 2        | ms003  | 1          |
| 8  | 2        | ms004  | 1          |
| 9  | 2        | fx001  | 4          |
| 10 | 2        | fx002  | 1          |

I’m performing the following consultation:

SELECT 
    SUM(tb.quantidade*quantidade)
FROM
    tabela_bom_itens
GROUP BY
    codigo;

What is to happen to tabela_itens is the parent product, and in tabela_bom_itens would be the children. In the tabela_bom_itens would be the amount of parts going to each parent item. So to know the amount of parts going into the parent product was to multiply the amount of parent product for each child product, and, if the same items appear in the two, group and inform the value of the sum of the items.

The expected result is:

| codigo | quantidade |
| mp001  | 1          |
| mp002  | 2          |
| ms001  | 2          |
| ms002  | 4          |
| ms003  | 2          |
| ms004  | 2          |
| fx001  | 9          |
| fx002  | 10         |

But what is returning has nothing to do, what is wrong with my query?

Answer 1

Note: I have a limitation that I don’t have a Mysql available to test, so I’m running tests on Sqlite; so if any syntax is incorrect, please correct/notify me

To know the relationship between parent products and products good children, we need to make a joint. So, for me to associate all the lines of tabela_bom_itens with the lines of tabela_itens, make an internal joint:

SELECT
    *
FROM
    tabela_itens pai
    INNER JOIN tabela_bom_itens filho
        ON (pai.id = filho.id_itens)

The internal merge is indicated by the syntax INNER JOIN. When making a junction, it is always good to say what the junction condition is. In this case, the junction condition is when the id the father’s equal to id_itens child. This joining condition is indicated in the ON (pai.id = filho.id_itens).

To do the multiplication to know how much of each child piece goes to the parent product (still without grouping), is to do the following multiplication:

SELECT
    pai.quantidade * filho.quantidade as quantidade_multiplicada,
    *
FROM
    tabela_itens pai
    INNER JOIN tabela_bom_itens filho
        ON (pai.id = filho.id_itens)

Note that the first column (identified by quantidade_multiplicada) is the desired value of to add up. Now, we need to add this multiplication, grouping by codigo:

SELECT
    filho.codigo,
    SUM(pai.quantidade * filho.quantidade) as quantidade_venda
FROM
    tabela_itens pai
    INNER JOIN tabela_bom_itens filho
        ON (pai.id = filho.id_itens)
GROUP BY filho.codigo

In the column quantidade_venda, we have how much of each item was sold.

In this case, I believe that the expected result has a misconception for the product fx002, because we have it was sold 5 times for the item Maquina Prensa (quantity multiplier 1) and 1 time for the item Maquina Secadora (quantity multiplier 2), which would result in 7.

This is the result of my consultation:

| codigo | quantidade_venda |
| fx001  | 9                |
| fx002  | 7                |
| mp001  | 1                |
| mp002  | 2                |
| ms001  | 2                |
| ms002  | 4                |
| ms003  | 2                |
| ms004  | 2                |