Table populated by other tables

Asked

Viewed 81 times

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:

inserir a descrição da imagem aqui

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?

php mysql

2 answers

1


From what I understand about what you want to do by example above, the way would be this is:

SELECT 
    B.CENTRO_CUSTO, SUM(A.VALOR), SUM(B.INSS + B.FGTS), SUM(C.COMISSAO + C.META + C.EXTRA)
FROM 
    INFORMATICA_SALARIO_BRUTO A
INNER JOIN 
    INFORMATICA_TRIBUTOS B ON B.CENTRO_CUSTO = A.CENTRO_CUSTO AND B.PERIODO = A.PERIODO
INNER JOIN 
    INFORMATICA_VARIAVEL C ON C.CENTRO_CUSTO = B.CENTRO_CUSTO AND C.PERIODO = B.PERIODO
WHERE  
    B.PERIODO = '2017-12-31'
GROUP BY
    B.CENTRO_CUSTO

  • 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.

  • Do you have a practical example to present? With the values in each table and what is the expected value in the answer?

  • 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.

  • 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.

1

To insert data from one or several columns in a new table we can use a INSERT INTO combined with a SELECT or syntax CREATE TABLE ... SELECT

Examples:

INSERT INTO tabela_a (coluna1, coluna2) SELECT coluna1,coluna2 FROM tabela_b INNER JOIN tabela_c ON tabela_b.id = tabela_c.id;

CREATE TABLE tabela_copia [AS] SELECT * FROM tabela_orininal;

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.