如何将这三个表与WHERE子句关联起来

Question

我有3个表，其中包含以下列：

ITV2_HIST_DEFECTOS_INSPECTION：(约500行(

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行(

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行(

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

我试图将这3个表与这个查询联系起来：

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 

但我收到了100000个结果。错误在哪里？

Answer 1

如果没有其他信息，很难判断，但您的逻辑看起来是正确的：ITV2_HIST_VEHICULOS1:nITV2_HIST_INSPECCIONES1:nITV2_HIST_DEFECTOS_INSPECCION，这应该不会从缺陷表中返回超过500行。

添加DISTINCT可能会得到正确的结果，但仍然存在问题。

由于您不返回缺陷详细信息，您只想了解在检查过程中发现缺陷的车辆：

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
)

编辑：

由于同一辆车有多排，您需要在加入前应用DISTINCT：

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
)