How to relate these three tables with WHERE clause?

Question

I have 3 tables with these columns:

ITV2_HIST_DEFECTOS_INSPECCION: (~500 rows)

C_ESTACION_ID (FK)
N_ANNO
C_INSPECCION_ID (FK)
C_SECCION_ID
C_DEFECTO_ID   (PK)
C_DEFECTO
C_CALIFICACION
U_CREACION
F_CREACION
U_MODIF
F_MODIF
N_ORDEN

ITV2_HIST_INSPECCIONES (~400 rows)

C_ESTACION_ID (FK)
N_ANNO
C_INSPECCION_ID (PK)
C_VEHICULO_ID (FK)
N_FASE
N_KILOMETROS
N_HORAS
C_RESULTADO
C_TIPO_INSPECC_ID (FK)
C_TIPO_INSPECCION

ITV2_HIST_VEHICULOS (~500 rows)

C_VEHICULO_ID (PK)
F_CAMBIO
N_BASTIDOR
A_MATRICULA
A_MARCA
A_MODELO
C_CLASIF_VEH_CONS_ID (FK)
C_CLASIF_VEH_USO_ID (FK)
C_CATEGORIA_ID (FK)
A_TIPO_VEHICULO

I try to relate the 3 tables with this query:

 SELECT         
    INSP.C_ESTACION_ID AS ESTACION,
    INSP.C_RESULTADO AS RESULTADO,
    INSP.C_VEHICULO_ID AS C_VEHICULO_ID,
    VEHI.A_MATRICULA AS MATRICULA                            
    FROM 
    ITV2_HIST_DEFECTOS_INSPECCION DEF,
    ITV2_HIST_INSPECCIONES INSP, 
    ITV2_HIST_VEHICULOS VEHI
    WHERE               
    DEF.C_INSPECCION_ID=INSP.C_INSPECCION_ID 
    AND DEF.C_ESTACION_ID= INSP.C_ESTACION_ID
    AND INSP.C_VEHICULO_ID=VEHI.C_VEHICULO_ID

But I receive 100.000 results. Where's the error?

[Explicit vs Implicit joins](https://stackoverflow.com/questions/53061517/explicit-joins-vs-implicit-joins) — jean, Nov 05 '18 at 11:10
Could you provide a few sample records in each of the tables, preferably with matching keys? — Mehdi, Nov 05 '18 at 12:23
@MehdiElFadil For `C_INSPECCION_ID: 56.919` I receive a 2 rows in `ITV2_HIST_DEFECTOS` I receive a 1 row in `ITV2_HIST_INSPECCIONES` return me `C_VEHICULO_ID=988` and in `ITV2_HIST_VEHICULOS` I received 2 rows but in query I received 4 rows for that `C_INSPECCION_ID` and `C_VEHICULO_ID` — Raulitus, Nov 05 '18 at 12:43
If you got 2 rows for `C_VEHICULO_ID=988` in `ITV2_HIST_VEHICULOS` your Primary Key is not based on `C_VEHICULO_ID` — dnoeth, Nov 05 '18 at 13:00
Well, you wrote `PK`. For one `C_VEHICULO_ID` you got *n* rows in `ITV2_HIST_VEHICULOS` and *m* rows in `ITV2_HIST_INSPECCIONES`, resulting in *n*m* rows in your result. — dnoeth, Nov 05 '18 at 13:32

dnoeth · Accepted Answer · 2018-11-05T13:37:10.980

Without additional information it's hard to tell, but your logic looks right: ITV2_HIST_VEHICULOS 1:n ITV2_HIST_INSPECCIONES 1:n ITV2_HIST_DEFECTOS_INSPECCION, this should return no more than the 500 rows from the defects table.

Adding DISTINCT might get you the right result, but there's still something wrong.

As you don't return defect details you only want to know about vehicles with defects found during inspection:

 SELECT         
    INSP.C_ESTACION_ID AS ESTACION,
    INSP.C_RESULTADO AS RESULTADO,
    INSP.C_VEHICULO_ID AS C_VEHICULO_ID,
    VEHI.A_MATRICULA AS MATRICULA                            
 FROM ITV2_HIST_VEHICULOS VEHI
 JOIN ITV2_HIST_INSPECCIONES INSP
   ON INSP.C_VEHICULO_ID=VEHI.C_VEHICULO_ID 
 WHERE EXISTS
  ( -- any defects recorded for this inspection?
    SELECT * 
    FROM ITV2_HIST_DEFECTOS_INSPECCION DEF
    WHERE DEF.C_INSPECCION_ID=INSP.C_INSPECCION_ID 
      AND DEF.C_ESTACION_ID= INSP.C_ESTACION_ID
  )

Edit:

As you got multiple rows for the same vehicle, you need to apply DISTINCT before the join:

 SELECT         
    INSP.C_ESTACION_ID AS ESTACION,
    INSP.C_RESULTADO AS RESULTADO,
    INSP.C_VEHICULO_ID AS C_VEHICULO_ID,
    VEHI.A_MATRICULA AS MATRICULA                            
 FROM ITV2_HIST_INSPECCIONES INSP
 JOIN
   ( -- hopefully there's only one A_MATRICULA per C_VEHICULO_ID
     -- otherwise switch to aggregation
     -- SELECT C_VEHICULO_ID, MAX(A_MATRICULA)
     -- FROM ITV2_HIST_VEHICULOS
     -- GROUP BY C_VEHICULO_ID
     SELECT DISTINCT C_VEHICULO_ID, A_MATRICULA
     FROM ITV2_HIST_VEHICULOS
   ) VEHI
 ON INSP.C_VEHICULO_ID=VEHI.C_VEHICULO_ID 
 WHERE EXISTS
  ( -- any defects recorded for this inspection?
    SELECT * 
    FROM ITV2_HIST_DEFECTOS_INSPECCION DEF
    WHERE DEF.C_INSPECCION_ID=INSP.C_INSPECCION_ID 
      AND DEF.C_ESTACION_ID= INSP.C_ESTACION_ID
  )

I can't use PLSQL. In DBeaver editor I have a error with the sintax. Anyway in sqldeveloper I receive ~500.000 rows — Raulitus, Nov 05 '18 at 12:12
In `JOIN` I have the error ORA-00933 SQL Command Not Properly Ended — Raulitus, Nov 05 '18 at 12:46
When you join inspections and vehicles only (of course you can use old-style syntax) you should get 400 rows. — dnoeth, Nov 05 '18 at 12:52
Anyway I have 500.000 rows in SQL Developer with this query (I thought that was PLSQL) — Raulitus, Nov 05 '18 at 12:53
I tried it in other schema with same structure of tables Result: `ITV2_HIST_INSPECCIONES:2805 rows` `ITV2_HIST_DEFECTOS_INSPECCIONES: 2808 rows` `ITV2_HIST_VEHICULOS:2819 rows` With your query I reveived 525.638 rows as result — Raulitus, Nov 05 '18 at 13:10

How to relate these three tables with WHERE clause?

1 Answers1