Table filled by other tables

Question

Table filled by other tables

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I need to create a table with PHP and MySQL that is populated by other tables, all tables have a cost center column, I also did a column code, for each cost center, 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 similar tables as the insert above, I need to fill in another one, which should look like this:

Asyoucanseeintheimagenoteverytablehasallcostcenters,testingthisSELECT:

SELECTb.centro_custo,SUM(a.valor),SUM(b.inss+b.fgts),SUM(c.comissao+c.meta+c.extra)FROMinformatica_salario_brutoa,informatica_tributosb,informatica_variavelcWHEREb.centro_custo=a.centro_custoANDb.centro_custo=c.centro_custoANDb.periodo="2017-12-31"
AND b.periodo = a.periodo
AND b.periodo = c.periodo
GROUP BY b.centro_custo;

It does not return all center_cost of B, only those that are common between 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 multiple times and thus leaving the wrong values, SUM also seems to confuse, adding values with same cost center of different tables.

Edit: Another problem I encounter is that when I print the data in PHP the query is stored in a vector ie the data comes out in sequence, so in the example of the image the VALUE 1st TABLE jumps the middle line, because this cost center has no value, how can you do this?

php mysql

asked by anonymous 26.02.2018 / 12:53

2 answers

1

To insert data from one or more columns into a new table, we can use a INSERT INTO combined with a SELECT or the 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;

26.02.2018 / 16:15

Change value of two variables without using a third, using only sum or subtraction [closed] How to do a SELECT while keeping the first column until all records

score 1 · Accepted Answer

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

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