Myujth

I want to use within a FROM a subset of 2 tables using RIGHT JOIN (I want from that subset all the rows of ITV2_VEHICULOS whose ID is not in ITV2_HIST_VEHICULOS) so that the SELECT "takes" the data from there and with the WHERE it can filter

My query:

SELECT
 *
FROM
 ITV2_INSPECCIONES I,
 ITV2_HORAS_INSPECCION HI_FIN,
 ITV2_INSPECCIONES I_SIG,
 ITV2_HORAS_INSPECCION HI_SIG_INI,
 ITV2_HIST_VEHICULOS VH,
 ITV2_CATEGORIAS_VEHICULO CAT,
 ITV2_CLASIF_VEH_CONS CVC,
 ITV2_CLASIF_VEH_USO CVU,
 (
 SELECT
 *
 FROM
 ITV2_HIST_VEHICULOS VH
 RIGHT JOIN ITV2_VEHICULOS V ON
 VH.C_VEHICULO_ID = V.C_VEHICULO_ID
 ) VI 

WHERE
 I.C_TIPO_INSPECCION = 1 
 AND I.F_DESFAVORABLE IS NOT NULL
 AND I.C_RESULTADO IN(
 3,
 4
 )
 AND I.C_VEHICULO_ID = VI.C_VEHICULO_ID 
 AND VI.C_CATEGORIA_ID = CAT.C_CATEGORIA_ID
 AND VI.C_CLASIF_VEH_CONS_ID = CVC.C_CLASIF_VEH_CONS_ID
 AND VI.C_CLASIF_VEH_USO_ID = CVU.C_CLASIF_VEH_USO_ID -- HORAS 
 AND I.C_ESTACION_ID = HI_FIN.C_ESTACION_ID
 AND I.C_INSPECCION_ID = HI_FIN.C_INSPECCION_ID
 AND I.N_ANNO = HI_FIN.N_ANNO
 AND HI_FIN.C_TIPO_HORA_ID = 6 -- INSPECCION SIGUIENTE 
 AND I.C_ESTACION_ID = I_SIG.C_ESTACION_ID_FASE_ANT
 AND I.C_INSPECCION_ID = I_SIG.C_INSPECCION_ID_FASE_ANT
 AND I.N_ANNO = I_SIG.N_ANNO_FASE_ANT -- 
 AND I_SIG.N_ANNO IN(
 2013,
 2014,
 2015,
 2016,
 2017,
 2018
 )
 AND I_SIG.C_ESTACION_ID IN(
 3,
 21,
 22,
 26,
 28,
 32,
 34,
 37,
 41,
 47,
 53,
 59,
 60
 )
 AND I_SIG.F_INSPECCION >= '01/09/2015'
 AND I_SIG.F_INSPECCION <= '30/09/2018' --

 AND I_SIG.F_DESFAVORABLE IS NULL
 AND I_SIG.C_RESULTADO IN(
 1,
 2
 ) -- Y HORAS

 AND I_SIG.C_ESTACION_ID = HI_SIG_INI.C_ESTACION_ID
 AND I_SIG.C_INSPECCION_ID = HI_SIG_INI.C_INSPECCION_ID
 AND I_SIG.N_ANNO = HI_SIG_INI.N_ANNO
 AND HI_SIG_INI.C_TIPO_HORA_ID = 1

 --GROUP BY...

I expect in the output:

C_ESTACION_ID(FROM I) |C_VEHICULO_ID(FROM(I) |C_TIPO_HORA_ID(FROM HI_FIN)|F_HORA (FROM I_FIN) |A_MATRICULA FROM (V) | F_CAMBIO FROM (VH -> IF subdata of V EXISTS)
 ---------------------|----------------------|---------------------------|--------------------|---------------------|---------------------------------------

This is what your query would look like if you use "explicit join syntax" instead of just some commas between table names:

SELECT *
FROM ITV2_INSPECCIONES I
INNER JOIN ITV2_HORAS_INSPECCION HI_FIN ON I.C_ESTACION_ID = HI_FIN.C_ESTACION_ID
 AND I.C_INSPECCION_ID = HI_FIN.C_INSPECCION_ID
 AND I.N_ANNO = HI_FIN.N_ANNO
INNER JOIN ITV2_INSPECCIONES I_SIG ON I.C_ESTACION_ID = I_SIG.C_ESTACION_ID_FASE_ANT
 AND I.C_INSPECCION_ID = I_SIG.C_INSPECCION_ID_FASE_ANT
 AND I.N_ANNO = I_SIG.N_ANNO_FASE_ANT
INNER JOIN ITV2_HORAS_INSPECCION HI_SIG_INI ON I_SIG.C_ESTACION_ID = HI_SIG_INI.C_ESTACION_ID
 AND I_SIG.C_INSPECCION_ID = HI_SIG_INI.C_INSPECCION_ID
 AND I_SIG.N_ANNO = HI_SIG_INI.N_ANNO
WHERE I.C_TIPO_INSPECCION = 1
 AND I.F_DESFAVORABLE IS NOT NULL
 AND I.C_RESULTADO IN (3, 4)
 AND HI_FIN.C_TIPO_HORA_ID = 6 -- INSPECCION SIGUIENTE 
 AND HI_SIG_INI.C_TIPO_HORA_ID = 1
 AND I_SIG.F_INSPECCION >= '01/09/2015'
 AND I_SIG.F_INSPECCION <= '30/09/2018'
 AND I_SIG.F_DESFAVORABLE IS NULL
 AND I_SIG.N_ANNO IN (2013, 2014, 2015, 2016, 2017, 2018)
 AND I_SIG.C_ESTACION_ID IN (3, 21, 22, 26, 28, 32, 34, 37, 41, 47, 53, 59, 60)
 AND I_SIG.C_RESULTADO IN (1, 2) -- Y HORAS

Now I had to pull out several tables and the subquery from that because, frankly, they don't make much sense to me:

ITV2_HIST_VEHICULOS VH, << no join conditions to preceding tables
ITV2_CATEGORIAS_VEHICULO CAT, << no join conditions to preceding tables
ITV2_CLASIF_VEH_CONS CVC, << no join conditions to preceding tables
ITV2_CLASIF_VEH_USO CVU, << no join conditions to preceding tables
(
 SELECT
 *
 FROM ITV2_VEHICULOS V
 LEFT JOIN ITV2_HIST_VEHICULOS VH ON
 VH.C_VEHICULO_ID = V.C_VEHICULO_ID
) VI
AND I.C_VEHICULO_ID = VI.C_VEHICULO_ID 
AND VI.C_CATEGORIA_ID = CAT.C_CATEGORIA_ID
AND VI.C_CLASIF_VEH_CONS_ID = CVC.C_CLASIF_VEH_CONS_ID
AND VI.C_CLASIF_VEH_USO_ID = CVU.C_CLASIF_VEH_USO_ID