Problems with a LEFT JOIN

Question

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

Your question is actually incomplete, in order to help you, we need a sample data, expected output and current output. A [Fiddle](https://www.db-fiddle.com/) is most than welcome. See [Tips for asking a good Structured Query Language (SQL) question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question) — Cid, Jul 09 '22 at 09:53
Please in code questions give a [mre]--cut & paste & runnable code & example input; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Jul 11 '22 at 20:09

score 0 · Answer 1 · answered Jul 09 '22 at 10:14

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.

score 0 · Answer 2 · answered Jul 11 '22 at 16:48

0

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

answered Jul 11 '22 at 16:48

Anees Mohideen

16

1

[Such Venn-like diagrams for joins are unclear, unhelpful & misleading.](https://stackoverflow.com/a/55642928/3404097) [See my Q&A comments here.](https://stackoverflow.com/q/38549/3404097) All you have to do to see this is try to write a correct legend for one. Don't forget that SQL tables are bags not sets. Joins are on any condition & don't need constraints so at best the diagrams when explained address special cases and/or partial properties. PS Sites with such diagrams are poor resources. PS Give credit when you copy from other places. [answer] [Help] – philipxy Jul 11 '22 at 20:12

Problems with a LEFT JOIN

2 Answers2