, , . WHERE "Import_AcesApplication_New.base_vehicle_id" , ...
"Import_AcesApplication_New". , , , , .
... "STRAIGHT_JOIN" , .
select STRAIGHT_JOIN
fields, fields, fields
from
yourTables
JOIN Other Tables on ...
, , , STRAIGHT_JOIN, . ?
, ... , , .
SELECT STRAIGHT_JOIN
IAN.sku,
IAN.notes,
Parts.partterminologyname,
BaseVehicle.YearID,
Make.MakeName,
Model.modelname,
Submodel.SubmodelName,
CONCAT(EngineBase.Cylinders, ' Cyl ', EngineBase.Liter, 'L') as engine,
Positions.position,
BedLength.BedLength,
BedLength.BedLengthMetric,
BedType.BedTypeName,
BodyNumDoors.BodyNumDoors,
BodyType.BodyTypeName,
FrontBrakeType.BrakeTypeName,
RearBrakeType.BrakeTypeName,
BrakeSystem.BrakeSystemName,
BrakeABS.BrakeABSName,
DriveType.DriveTypeName,
EngineDesignation.EngineDesignationName,
EngineVIN.EngineVINName,
Valves.ValvesPerEngine,
EngineBase.Liter,
EngineBase.CC,
EngineBase.CID,
EngineBase.Cylinders,
EngineBase.BlockType,
EngineBase.EngBoreIn,
EngineBase.EngBoreMetric,
EngineBase.EngStrokeIn,
EngineBase.EngStrokeMetric,
FuelDeliveryType.FuelDeliveryTypeName,
FuelDeliverySubType.FuelDeliverySubTypeName,
FuelSystemControlType.FuelSystemControlTypeName,
FuelSystemDesign.FuelSystemDesignName,
Aspiration.AspirationName,
CylinderHeadType.CylinderHeadTypeName,
FuelType.FuelTypeName,
IgnitionSystemType.IgnitionSystemTypeName,
EngineMfr.MfrName,
EngineVersion.EngineVersion,
PowerOutput.HorsePower,
PowerOutput.KilowattPower,
SpringType.SpringTypeName,
SteeringType.SteeringTypeName,
SteeringSystem.SteeringSystemName,
TransmissionType.TransmissionTypeName,
TransmissionNumSpeeds.TransmissionNumSpeeds,
TransmissionMfrCode.TransmissionMfrCode,
TransElecControlled.ElecControlled,
TransmissionMfr.MfrName,
WheelBase.WheelBase,
WheelBase.WheelBaseMetric,
VehicleType.VehicleTypeName,
VehicleTypeGroup.VehicleTypeGroupName
FROM
Import_AcesApplication_New IAN
JOIN BaseVehicle
ON IAN.base_vehicle_id = BaseVehicle.BaseVehicleID
JOIN Make
ON BaseVehicle.MakeID = Make.MakeID
JOIN Model
ON BaseVehicle.ModelID = Model.ModelID
JOIN VehicleType
ON Model.VehicleTypeID = VehicleType.VehicleTypeID
JOIN VehicleTypeGroup
ON VehicleType.VehicleTypeGroupId = VehicleTypeGroup.VehicleTypeGroupId
JOIN Parts
ON IAN.part_type_id = Parts.PartTerminologyID
JOIN Vehicle
ON IAN.base_vehicle_id = Vehicle.BaseVehicleID
JOIN Submodel
ON Vehicle.SubmodelID = Submodel.SubmodelID
JOIN VehicleToBedConfig
ON Vehicle.VehicleID = VehicleToBedConfig.VehicleID
JOIN BedConfig
ON VehicleToBedConfig.BedConfigID = BedConfig.BedConfigID
JOIN BedLength
ON BedConfig.BedLengthID = BedLength.BedLengthID
JOIN BedType
ON BedConfig.BedTypeID = BedType.BedTypeID
JOIN VehicleToBodyStyleConfig
ON Vehicle.VehicleID = VehicleToBodyStyleConfig.VehicleID
JOIN BodyStyleConfig
ON VehicleToBodyStyleConfig.BodyStyleConfigID = BodyStyleConfig.BodyStyleConfigID
JOIN BodyNumDoors
ON BodyStyleConfig.BodyNumDoorsID = BodyNumDoors.BodyNumDoorsID
JOIN BodyType
ON BodyStyleConfig.BodyTypeID = BodyType.BodyTypeID
JOIN VehicleToBrakeConfig
ON Vehicle.VehicleID = VehicleToBrakeConfig.VehicleID
JOIN BrakeConfig
ON VehicleToBrakeConfig.BrakeConfigID = BrakeConfig.BrakeConfigID
JOIN BrakeType FrontBrakeType
ON BrakeConfig.FrontBrakeTypeID = FrontBrakeType.BrakeTypeID
JOIN BrakeType RearBrakeType
ON BrakeConfig.RearBrakeTypeID = RearBrakeType.BrakeTypeID
JOIN BrakeSystem
ON BrakeConfig.BrakeSystemID = BrakeSystem.BrakeSystemID
JOIN BrakeABS
ON BrakeConfig.BrakeABSID = BrakeABS.BrakeABSID
JOIN VehicleToDriveType
ON Vehicle.VehicleID = VehicleToDriveType.VehicleID
JOIN DriveType
ON VehicleToDriveType.DriveTypeID = DriveType.DriveTypeID
JOIN VehicleToEngineConfig
ON Vehicle.VehicleID = VehicleToEngineConfig.VehicleID
JOIN EngineConfig
ON VehicleToEngineConfig.EngineConfigID = EngineConfig.EngineConfigID
JOIN EngineBase
ON EngineConfig.EngineBaseID = EngineBase.EngineBaseID
JOIN EngineDesignation
ON EngineConfig.EngineDesignationID = EngineDesignation.EngineDesignationID
JOIN EngineVIN
ON EngineConfig.EngineVINID = EngineVIN.EngineVINID
JOIN Valves
ON EngineConfig.ValvesID = Valves.Valvesid
JOIN FuelDeliveryConfig
ON EngineConfig.FuelDeliveryConfigID = FuelDeliveryConfig.FuelDeliveryConfigID
JOIN FuelDeliveryType
ON FuelDeliveryConfig.FuelDeliveryTypeID = FuelDeliveryType.FuelDeliveryTypeID
JOIN FuelDeliverySubType
ON FuelDeliveryConfig.FuelDeliverySubTypeID = FuelDeliverySubType.FuelDeliverySubTypeID
JOIN FuelSystemControlType
ON FuelDeliveryConfig.FuelSystemControlTypeID = FuelSystemControlType.FuelSystemControlTypeID
JOIN FuelSystemDesign
ON FuelDeliveryConfig.FuelSystemDesignID = FuelSystemDesign.FuelSystemDesignID
JOIN Aspiration
ON EngineConfig.AspirationID = Aspiration.AspirationID
JOIN CylinderHeadType
ON EngineConfig.CylinderHeadTypeID = CylinderHeadType.CylinderHeadTypeID
JOIN FuelType
ON EngineConfig.FuelTypeID = FuelType.FuelTypeID
JOIN IgnitionSystemType
ON EngineConfig.IgnitionSystemTypeID = IgnitionSystemType.IgnitionSystemTypeID
JOIN Mfr EngineMfr
ON EngineConfig.EngineMfrID = EngineMfr.MfrID
JOIN EngineVersion
ON EngineConfig.EngineVersionID = EngineVersion.EngineVersionID
JOIN PowerOutput
ON EngineConfig.PowerOutputId = PowerOutput.PowerOutputId
JOIN VehicleToSpringTypeConfig
ON Vehicle.VehicleID = VehicleToSpringTypeConfig.VehicleID
JOIN SpringType
ON VehicleToSpringTypeConfig.SpringTypeConfigID = SpringType.SpringTypeID
JOIN VehicleToSteeringConfig
ON Vehicle.VehicleID = VehicleToSteeringConfig.VehicleID
JOIN SteeringConfig
ON VehicleToSteeringConfig.SteeringConfigID = SteeringConfig.SteeringConfigID
JOIN SteeringType
ON SteeringConfig.SteeringConfigID = SteeringType.SteeringTypeID
JOIN SteeringSystem
ON SteeringConfig.SteeringSystemID = SteeringSystem.SteeringSystemID
JOIN VehicleToTransmission
ON Vehicle.VehicleID = VehicleToTransmission.VehicleID
JOIN Transmission
ON VehicleToTransmission.TransmissionID = Transmission.TransmissionID
JOIN TransmissionBase
ON Transmission.TransmissionBaseID = TransmissionBase.TransmissionBaseID
JOIN TransmissionType
ON TransmissionBase.TransmissionTypeID = TransmissionType.TransmissionTypeID
JOIN TransmissionNumSpeeds
ON TransmissionBase.TransmissionNumSpeedsID = TransmissionNumSpeeds.TransmissionNumSpeedsID
JOIN TransmissionControlType
ON TransmissionBase.TransmissionControlTypeID = TransmissionControlType.TransmissionControlTypeID
JOIN ElecControlled TransElecControlled
ON Transmission.TransmissionElecControlledID = TransElecControlled.ElecControlledID
JOIN Mfr TransmissionMfr
ON Transmission.TransmissionMfrID = TransmissionMfr.MfrID
JOIN TransmissionMfrCode
ON Transmission.TransmissionMfrCodeID = TransmissionMfrCode.TransmissionMfrCodeID
JOIN VehicleToWheelbase
ON Vehicle.VehicleID = VehicleToWheelbase.VehicleID
JOIN WheelBase
ON VehicleToWheelbase.WheelbaseID = WheelBase.WheelBaseID
JOIN Positions
ON IAN.position_id = Positions.PositionID
, , , "prequery", vehicle_id. , , , , , .., ...
SELECT STRAIGHT_JOIN
IAN.sku,
IAN.notes,
Parts.partterminologyname,
PQBaseVehicle.YearID,
PQBaseVehicle.MakeName,
PQBaseVehicle.modelname,
PQBaseVehicle.VehicleTypeName,
PQBaseVehicle.VehicleTypeGroupName,
PQVTrans.TransmissionTypeName,
PQVTrans.TransmissionNumSpeeds,
PQVTrans.ElecControlled,
PQVTrans.MfrName,
PQVTrans.TransmissionMfrCode,
PQEngine.Engine,
PQEngine.Liter,
PQEngine.CC,
PQEngine.CID,
PQEngine.Cylinders,
PQEngine.BlockType,
PQEngine.EngBoreIn,
PQEngine.EngBoreMetric,
PQEngine.EngStrokeIn,
PQEngine.EngStrokeMetric,
PQEngine.EngineDesignationName,
PQEngine.EngineVINName,
PQEngine.ValvesPerEngine,
PQEngine.FuelDeliveryTypeName,
PQEngine.FuelDeliverySubTypeName,
PQEngine.FuelSystemControlTypeName,
PQEngine.FuelSystemDesignName,
PQEngine.AspirationName,
PQEngine.CylinderHeadTypeName,
PQEngine.FuelTypeName,
PQEngine.IgnitionSystemTypeName,
PQEngine.MfrName,
PQEngine.EngineVersion,
PQEngine.HorsePower,
PQEngine.KilowattPower,
PQBrakes.FrontBrakeTypeName,
PQBrakes.RearBrakeTypeName,
PQBrakes.BrakeSystemName,
PQBrakes.BrakeABSName,
PQBody.BodyNumDoors,
PQBody.BodyTypeName,
PQBeds.BedLength,
PQBeds.BedLengthMetric,
PQBeds.BedTypeName,
PQSteering.SteeringTypeName,
PQSteering.SteeringSystemName,
Submodel.SubmodelName,
Positions.position,
DriveType.DriveTypeName,
SpringType.SpringTypeName,
WheelBase.WheelBase,
WheelBase.WheelBaseMetric
FROM
Import_AcesApplication_New IAN
JOIN Parts
ON IAN.part_type_id = Parts.PartTerminologyID
JOIN Positions
ON IAN.position_id = Positions.PositionID
JOIN ( select STRAIGHT_JOIN
BaseVehicle.BaseVehicleID,
BaseVehicle.YearID,
Make.MakeName,
Model.modelname,
VehicleType.VehicleTypeName,
VehicleTypeGroup.VehicleTypeGroupName
from
BaseVehicle
JOIN Make
ON BaseVehicle.MakeID = Make.MakeID
JOIN Model
ON BaseVehicle.ModelID = Model.ModelID
JOIN VehicleType
ON Model.VehicleTypeID = VehicleType.VehicleTypeID
JOIN VehicleTypeGroup
ON VehicleType.VehicleTypeGroupId = VehicleTypeGroup.VehicleTypeGroupId ) PQBaseVehicle
ON IAN.base_vehicle_id = PQBaseVehicle.BaseVehicleID
JOIN Vehicle
ON IAN.base_vehicle_id = Vehicle.BaseVehicleID
JOIN Submodel
ON Vehicle.SubmodelID = Submodel.SubmodelID
JOIN VehicleToWheelbase
ON Vehicle.VehicleID = VehicleToWheelbase.VehicleID
JOIN WheelBase
ON VehicleToWheelbase.WheelbaseID = WheelBase.WheelBaseID
JOIN VehicleToDriveType
ON Vehicle.VehicleID = VehicleToDriveType.VehicleID
JOIN DriveType
ON VehicleToDriveType.DriveTypeID = DriveType.DriveTypeID
JOIN VehicleToSpringTypeConfig
ON Vehicle.VehicleID = VehicleToSpringTypeConfig.VehicleID
JOIN SpringType
ON VehicleToSpringTypeConfig.SpringTypeConfigID = SpringType.SpringTypeID
JOIN ( SELECT STRAIGHT_JOIN
VSC.VehicleID,
ST.SteeringTypeName,
SS.SteeringSystemName
FROM
VehicleToSteeringConfig VSC
JOIN SteeringConfig SC
ON VSC.SteeringConfigID = SC.SteeringConfigID
JOIN SteeringType ST
ON SC.SteeringConfigID = ST.SteeringTypeID
JOIN SteeringSystem SS
ON SC.SteeringSystemID = SS.SteeringSystemID ) PQSteering
ON Vehicle.VehicleID = PQSteering.VehicleID
JOIN ( SELECT STRAIGHT_JOIN
VBC.VehicleID,
BL.BedLength,
BL.BedLengthMetric,
BT.BedTypeName
FROM
VehicleToBedConfig VBC
ON Vehicle.VehicleID = VBC.VehicleID
JOIN BedConfig BC
ON VBC.BedConfigID = BC.BedConfigID
JOIN BedLength BL
ON BC.BedLengthID = BL.BedLengthID
JOIN BedType BT
ON BC.BedTypeID = BT.BedTypeID ) PQBeds
ON Vehicle.VehicleID = PQBeds.VehicleID
JOIN ( SELECT STRAIGHT_JOIN
VBSC.VehicleID,
BND.BodyNumDoors,
BT.BodyTypeName
FROM
VehicleToBodyStyleConfig VBSC
JOIN BodyStyleConfig BSC
ON VBSC.BodyStyleConfigID = BSC.BodyStyleConfigID
JOIN BodyNumDoors BND
ON BSC.BodyNumDoorsID = BND.BodyNumDoorsID
JOIN BodyType BT
ON BSC.BodyTypeID = BT.BodyTypeID ) PQBody
ON Vehicle.VehicleID = PQBody.VehicleID
JOIN ( SELECT STRAIGHT_JOIN
VBC.VehicleID,
FBT.BrakeTypeName FrontBrakeTypeName,
RBT.BrakeTypeName RearBrakeTypeName,
BS.BrakeSystemName,
BrakeABS.BrakeABSName
FROM
VehicleToBrakeConfig VBC
JOIN BrakeConfig BC
ON VBC.BrakeConfigID = BC.BrakeConfigID
JOIN BrakeType FBT
ON BC.FrontBrakeTypeID = FBT.BrakeTypeID
JOIN BrakeType RBT
ON BC.RearBrakeTypeID = RBT.BrakeTypeID
JOIN BrakeSystem BS
ON BC.BrakeSystemID = BS.BrakeSystemID
JOIN BrakeABS
ON BC.BrakeABSID = BrakeABS.BrakeABSID ) PQBrakes
ON Vehicle.VehicleID = PQBrakes.VehicleID
JOIN ( select STRAIGHT_JOIN
VEC.VehicleID,
CONCAT(EB.Cylinders, ' Cyl ', EB.Liter, 'L') as engine,
EB.Liter,
EB.CC,
EB.CID,
EB.Cylinders,
EB.BlockType,
EB.EngBoreIn,
EB.EngBoreMetric,
EB.EngStrokeIn,
EB.EngStrokeMetric,
ED.EngineDesignationName,
EVIN.EngineVINName,
V.ValvesPerEngine,
FDT.FuelDeliveryTypeName,
FDST.FuelDeliverySubTypeName,
FSCT.FuelSystemControlTypeName,
FSD.FuelSystemDesignName,
Asp.AspirationName,
CHT.CylinderHeadTypeName,
FT.FuelTypeName,
IST.IgnitionSystemTypeName,
EMFR.MfrName,
EVer.EngineVersion,
PO.HorsePower,
PO.KilowattPower
from
VehicleToEngineConfig VEC
JOIN EngineConfig EC
ON VEC.EngineConfigID = EC.EngineConfigID
JOIN EngineBase EB
ON EC.EngineBaseID = EB.EngineBaseID
JOIN EngineDesignation ED
ON EC.EngineDesignationID = ED.EngineDesignationID
JOIN EngineVIN EVIN
ON EC.EngineVINID = EVIN.EngineVINID
JOIN Valves V
ON EC.ValvesID = V.Valvesid
JOIN FuelDeliveryConfig FDC
ON EC.FuelDeliveryConfigID = FDC.FuelDeliveryConfigID
JOIN FuelDeliveryType FDT
ON FDC.FuelDeliveryTypeID = FDT.FuelDeliveryTypeID
JOIN FuelDeliverySubType FDST
ON FDC.FuelDeliverySubTypeID = FDST.FuelDeliverySubTypeID
JOIN FuelSystemControlType FSCT
ON FDC.FuelSystemControlTypeID = FSCT.FuelSystemControlTypeID
JOIN FuelSystemDesign FSD
ON FDC.FuelSystemDesignID = FSD.FuelSystemDesignID
JOIN Aspiration Asp
ON EC.AspirationID = Asp.AspirationID
JOIN CylinderHeadType CHT
ON EC.CylinderHeadTypeID = CHT.CylinderHeadTypeID
JOIN FuelType FT
ON EC.FuelTypeID = FT.FuelTypeID
JOIN IgnitionSystemType IST
ON EC.IgnitionSystemTypeID = IST.IgnitionSystemTypeID
JOIN Mfr EMFR
ON EC.EngineMfrID = EMFR.MfrID
JOIN EngineVersion EVer
ON EC.EngineVersionID = EVer.EngineVersionID
JOIN PowerOutput PO
ON EC.PowerOutputId = PO.PowerOutputId ) PQEngine
ON Vehicle.VehicleID = PQEngine.VehicleID
JOIN ( select STRAIGHT_JOIN
VTM.VehicleID,
TT.TransmissionTypeName,
TNS.TransmissionNumSpeeds,
TEC.ElecControlled,
Mfr.MfrName,
TMC.TransmissionMfrCode
from
VehicleToTransmission VTM
JOIN Transmission T
ON VTM.TransmissionID = T.TransmissionID
JOIN TransmissionBase TB
ON T.TransmissionBaseID = TB.TransmissionBaseID
JOIN TransmissionType TT
ON TB.TransmissionTypeID = TT.TransmissionTypeID
JOIN TransmissionNumSpeeds TNS
ON TB.TransmissionNumSpeedsID = TNS.TransmissionNumSpeedsID
JOIN TransmissionControlType TCT
ON TB.TransmissionControlTypeID = TCT.TransmissionControlTypeID
JOIN ElecControlled TEC
ON T.TransmissionElecControlledID = TEC.ElecControlledID
JOIN Mfr
ON T.TransmissionMfrID = Mfr.MfrID
JOIN TransmissionMfrCode TMC
ON T.TransmissionMfrCodeID = TMC.TransmissionMfrCodeID ) PQVTrans
ON Vehicle.VehicleID = PQVTrans.VehicleID
, , PREQUERIES , , , , , , . , , , , . , .
PQBaseVehicle
PQVTrans
PQEngine
PQBrakes
PQBody
PQBeds
PQSteering
, PQSteering, ...
SELECT STRAIGHT_JOIN
VSC.VehicleID,
ST.SteeringTypeName,
SS.SteeringSystemName
FROM
VehicleToSteeringConfig VSC
JOIN SteeringConfig SC
ON VSC.SteeringConfigID = SC.SteeringConfigID
JOIN SteeringType ST
ON SC.SteeringConfigID = ST.SteeringTypeID
JOIN SteeringSystem SS
ON SC.SteeringSystemID = SS.SteeringSystemID
, , .