3
I have the SQL below, which returns data that I will fill in some inputs of a form. The problem is that I have 20 items (pieces) in this table, but it may be empty because the user can fill any quantity. The description of the piece is in her table (pieces) and in the occurrences it has only the code (cod_peccin). How could I get him to bring the description where it’s filled out and where it doesn’t have anything? I tried to use left Join, but could not, below the original query:
NOTE: In Oracle used the (+) next to the variable that could be null and worked, in Mysql I did not find anything similar outside left Join.
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
