Doubt mySQL and Left Join

3

I have the following SQL, which returns data that will fill some inputs of a form. The problem is that I have 20 items (pieces) in this table, but it can be empty because the user can fill any amount. The description of the piece is in her table (pieces) and in the occurrences has only the code (cod_peccin). How could he make it bring the description where it is filled and where it has nothing to bring? I tried to use left join, but I could not find the original query below:

SELECT o.*, DATE_FORMAT(O.dt_abertura, '%d-%m-%Y') as dt_abertura, DATE_FORMAT(O.dt_fechamento, '%d-%m-%Y') as dt_fechamento,
               DATE_FORMAT(O.data_inicio_1, '%d-%m-%Y') as data_inicio_1, DATE_FORMAT(O.data_inicio_2, '%d-%m-%Y') as data_inicio_2,
               DATE_FORMAT(O.data_inicio_3, '%d-%m-%Y') as data_inicio_3, DATE_FORMAT(O.data_inicio_4, '%d-%m-%Y') as data_inicio_4,
               DATE_FORMAT(O.data_inicio_5, '%d-%m-%Y') as data_inicio_5, DATE_FORMAT(O.data_inicio_6, '%d-%m-%Y') as data_inicio_6,
               DATE_FORMAT(O.data_fim_1, '%d-%m-%Y') as data_fim_1, DATE_FORMAT(O.data_fim_2, '%d-%m-%Y') as data_fim_2,
               DATE_FORMAT(O.data_fim_3, '%d-%m-%Y') as data_fim_3, DATE_FORMAT(O.data_fim_4, '%d-%m-%Y') as data_fim_4,
               DATE_FORMAT(O.data_fim_5, '%d-%m-%Y') as data_fim_5, DATE_FORMAT(O.data_fim_6, '%d-%m-%Y') as data_fim_6,
               e.descricao as descrMaquina, s.nome_setor as descrSetor, P1.DESCRICAO as DESC_1, P2.DESCRICAO as DESC_2, P3.DESCRICAO as DESC_3,P4.DESCRICAO as DESC_4,P5.DESCRICAO as DESC_5,P6.DESCRICAO as DESC_6,
               P7.DESCRICAO as DESC_7,P8.DESCRICAO as DESC_8,P9.DESCRICAO as DESC_9,P10.DESCRICAO as DESC_10,P11.DESCRICAO as DESC_11,P12.DESCRICAO as DESC_12,P13.DESCRICAO as DESC_13,P14.DESCRICAO as DESC_14,
               P15.DESCRICAO as DESC_15,P16.DESCRICAO as DESC_16,P17.DESCRICAO as DESC_17,P18.DESCRICAO as DESC_18,P19.DESCRICAO as DESC_19,P20.DESCRICAO as DESC_20
               FROM ocorrencias O, equipamento E, pecas P1,pecas P2,pecas P3,pecas P4,pecas P5,pecas P6,pecas P7,pecas P8,pecas P9,pecas P10,pecas P11,pecas P12,pecas P13,pecas P14,pecas P15,pecas P16,
                    pecas P17,pecas P18,pecas P19,pecas P20, setores S
               WHERE
               E.COD_EQUIPAMENTO = O.MAQUINA and
               S.centro_custo = O.setor and
               O.cod = 1 AND
               P1.COD_PECCIN = O.ITEM_1 AND
               P2.COD_PECCIN = O.ITEM_2 AND
               P3.COD_PECCIN = O.ITEM_3 AND
               P4.COD_PECCIN = O.ITEM_4 AND
               P5.COD_PECCIN = O.ITEM_5 AND
               P6.COD_PECCIN = O.ITEM_6 AND
               P7.COD_PECCIN = O.ITEM_7 AND
               P8.COD_PECCIN = O.ITEM_8 AND
               P9.COD_PECCIN = O.ITEM_9 AND
               P10.COD_PECCIN = O.ITEM_10 AND
               P11.COD_PECCIN = O.ITEM_11 AND
               P12.COD_PECCIN = O.ITEM_12 AND
               P13.COD_PECCIN = O.ITEM_13 AND
               P14.COD_PECCIN = O.ITEM_14 AND
               P15.COD_PECCIN = O.ITEM_15 AND
               P16.COD_PECCIN = O.ITEM_16 AND
               P17.COD_PECCIN = O.ITEM_17 AND
               P18.COD_PECCIN = O.ITEM_18 AND
               P19.COD_PECCIN = O.ITEM_19 AND
               P20.COD_PECCIN = O.ITEM_20
    
asked by anonymous 07.03.2016 / 18:30

1 answer

4

You should do with INEERS JOINS, or LEFT JOINS, or RIGHT JOINS between tables, ie FROM table1 LEFT JOIN table2 ... LEFT JOIN table3 ...

I hope this image helps you:

    
07.03.2016 / 18:40