0
I need to create a table with PHP and Mysql that is filled by other tables, all tables have a column centro_cost, I also made a column code, for each centro_cost but I’m not sure how to implement it.
codigo
,periodo
,empresa
,centro_custo
,valor
EX: ('100','2017-12-31','RJ','ADM.SERVICOS', '5166.87'),
With the values of 3 tables similar to the above Insert, I need to fill another one, which should look like this:
As you can see in the image not every table has all cost centers, testing this SELECT:
SELECT b.centro_custo, SUM(a.valor), SUM(b.inss + b.fgts), SUM(c.comissao + c.meta + c.extra)
FROM informatica_salario_bruto a, informatica_tributos b, informatica_variavel c
WHERE b.centro_custo = a.centro_custo
AND b.centro_custo = c.centro_custo
AND b.periodo = "2017-12-31"
AND b.periodo = a.periodo
AND b.periodo = c.periodo
GROUP BY b.centro_custo;
It does not return all centro_cost of B, only those that are common among the 3 tables and the values come out multiplied, it seems that SQL creates the same cost center several times EX:
ADM ADM
ADM PRODUCAO
ADM ENGENHARIA
COMERCIAL ADM
COMERCIAL PRODUCAO
COMERCIAL ENGENHARIA
Creating the same value over and over again and thus leaving the wrong values, SUM also seems to confuse, adding the values with the same centre_cost of different tables.
Editing: Another problem I find is that when I print the data in PHP the query is saved in an array that is, the data comes out in sequence, so in the image example the VALUE 1º TABLE jumps the middle line, because this centro_cost has no value, as it is possible to do this?
Good morning, thanks for the reply. I checked your suggestion and a problem occurred, the values are wrong. Some come out multiplied by 3 others come out right, others multiplied by 10. I believe this is the problem I mentioned, cost centres multiply. Cost center B, has a column that the others don’t have and it hasn’t been "printed" either.
– Luigi Azevedo
Do you have a practical example to present? With the values in each table and what is the expected value in the answer?
– Paulo R. F. Amorim
I was able to solve the problem by creating 3 temporary tables, so it is not necessary to do the SUM with more than one table in SELECT, already get the added value.
– Luigi Azevedo
I understand, maybe it wasn’t even necessary to create these temps, but if you want to keep it that way, it’s okay. If the answer helped in any way gives a positive point to her.
– Paulo R. F. Amorim