Problems with a LEFT JOIN-CodePudding

I'm trying to do a left join in two tables, the principal one is a table with production orders and the other one contains the quantity of defective material in those production orders. It is possible to have more than one different type of defect in one production order, so that means more than one register in the second table for one production order. The relations between table are the field production order, machine, production phase, article and enterprise:

SELECT PM.FECHA_FABRICACION,
       --TRUNC(PM.FECHA_FABRICACION) AS FECHA,
       PM.ORDEN_DE_FABRICACION,
       PM.CODIGO_FAMILIA,
       PM.CODIGO_ARTICULO,
       PM.COD_MAQUINA,
       DECODE (PM.COD_MAQUINA,'AN001','ANODIZADO', 'GR001','ANODIZADO', 'ES001','ANODIZADO','PU001', 'ANODIZADO', 'ZZ141', 'ANODIZADO', PM.COD_MAQUINA) AS MAQUINA_PARTE,
       PM.DESC_MAQUINA,
       PM.CANTIDAD_ACEPTADA,
       PM.M2_ACEPTADOS,
       PM.M2_CONPEPTO,
       PM.M2_TOTAL,
       PM.M2_EXT,
       PM.KILOS_ACEPTADOS,
       PM.BARRAS_ACEPTADAS,
       PM.FASE_REALIZADA,
       PR.CODIGO_DEFECTO,
       PR.CANTIDAD_RECHAZADA,
       PR.LONGITUD,
       PR.KILOS_RECHAZADOS,
       PR.OBSERVACIONES
 FROM ST_VW_PRODUCCION_MAQUINAS PM
 LEFT JOIN P_INFO_RECHAZOS PR
 ON PM.CODIGO_EMPRESA = PR.CODIGO_EMPRESA
       AND PM.ORDEN_DE_FABRICACION = PR.ORDEN_DE_FABRICACION
       AND PM.CODIGO_ARTICULO = PR.CODIGO_ARTICULO
       AND PM.COD_MAQUINA = PR.CODIGO_MAQUINA
       AND PM.FASE_REALIZADA = PR.FASE
       AND PM.CODIGO_EMPRESA = '01'
       AND PM.FECHA_FABRICACION > TO_DATE('04/07/2022 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
       --AND TRUNC(PM.FECHA_FABRICACION) = TRUNC(PR.FECHA_RECHAZO);

However it's not working

CodePudding user response：

If you are specifically looking for rejections, you dont need a LEFT JOIN, but a normal INNER JOIN, meaning you ONLY WANT to see those projects where at least one, or more, rejections had occurred.

The query itself looks ok otherwise, just get rid of the LEFT clause of left join.

As for the function DECODE, that is using PM.COD_MAQUINA at both the begin and end of the list of codes. Dont know if that was intentional.

For date filtering, if you are only looking for a specific date without regard to any time portion, you could change it to YYYY-MM-DD format without using the TO_DATE() construct as in

AND PM.FECHA_FABRICACION > '2022-07-04'

If you still are getting errors, please EDIT your post and provide what the error message is.

CodePudding user response：

To explain further, you are using a LEFT JOIN, which is a type of outer join. An outer join means that all the results from one side that don't match will still be displayed, and will just be matched to null. In your case, production orders that aren't defective will be listed.

However, it seems that you want to only display results that match on both sides (production orders that do have defects). When you just JOIN then you will have the result you want.

Here's a helpful diagram