select v.VehicleID,
'|' AS [| MMY], m.MakeName,md.ModelName,y.YearName,s.SubmodelName,
'|' AS [| OurMMY], om.ourMakeName,omd.ourModelName,oy.OurYearName,obs.ourBaseSubmodelName,orm.ourRealModelName,osm.ourSubmodelName,
'|' AS [| OurAdd], ovt.OurVehicleTypeName, ovst.OurVehicleSubtypeName,
'|' AS [| Appearance], bl.BedLength,bl.BedLengthMetric,bat.BedTypeName,bnd.BodyNumDoors,bt.BodyTypeName,obt.OurBodyTypeName,mfr.MfrBodyCodeName,wb.WheelBase,wb.WheelBaseMetric,
'|' AS [| Engine], fsdt.FuelDeliverySubTypeName,fdt.FuelDeliveryTypeName,fsct.FuelSystemControlTypeName,fsd.FuelSystemDesignName,a.AspirationName,cht.CylinderHeadTypeName,eb.Liter,eb.CC,eb.CID,eb.BlockTypeCylinders, eb.Cylinders,eb.BlockType,eb.EngBoreIn,eb.EngBoreMetric,eb.EngStrokeIn,eb.EngStrokeMetric,ed.EngineDesignationName,MforEn.EngineMfrName,ev.EngineVersion,evn.EngineVINName,fl.FuelTypeName,ist.IgnitionSystemTypeName,po.HorsePower,po.KilowattPower,vs.ValvesPerEngine,
'|' AS [| Transmission], tct.TransmissionControlTypeName,tns.TransmissionNumSpeeds,tt.TransmissionTypeName,MforTr.TransmissionMfrName,tmc.TransmissionMfrCode,
'|' AS [| Brake], ba.BrakeABSName,bs.BrakeSystemName,BTforF.FrontBrakeTypeName,BTforR.RearBrakeTypeName ,
'|' AS [| DriveType], dt.DriveTypeName,
'|' AS [| Spring], fspt.FrontSpringTypeName, rspt.RearSpringTypeName,
'|' AS [| Steering], stt.SteeringTypeName, sts.SteeringSystemName
from Vehicle as v
left join BaseVehicle as bv ON bv.basevehicleid=v.basevehicleid
left join Make as m on m.MakeId=bv.MakeId
left join Model as md on bv.modelid=md.modelid
left join Year as y on bv.yearid=y.yearid
left join Submodel as s on v.submodelid=s.submodelid

left join VehicleToEngineConfig as vc on v.vehicleid=vc.vehicleid
left join EngineConfig as ec on vc.engineconfigid=ec.engineconfigid
left join EngineBase AS eb on ec.EngineBaseID=eb.EngineBaseID
left join FuelType as fl on ec.FuelTypeID=fl.FuelTypeID
left join FuelDeliveryConfig as fdc on ec.FuelDeliveryConfigId=fdc.FuelDeliveryConfigId
left join FuelDeliveryType as fdt on fdc.FuelDeliveryTypeId= fdt.FuelDeliveryTypeId
left join FuelDeliverySubType as fsdt on fdc.FuelDeliverySubTypeId= fsdt.FuelDeliverySubTypeId
left join FuelSystemControlType as fsct on fdc.FuelSystemControlTypeId= fsct.FuelSystemControlTypeId
left join FuelSystemDesign as fsd on fdc.FuelSystemDesignId= fsd.FuelSystemDesignId
left join Aspiration as a on ec.AspirationId=a.AspirationId
left join EngineVIN as evn on ec.EngineVINId=evn.EngineVINId
left join Valves as vs on ec.ValvesId=vs.ValvesId
left join EngineDesignation as ed on ec.EngineDesignationId=ed.EngineDesignationId
left join EngineVersion as ev on ec.EngineVersionId=ev.EngineVersionId
left join PowerOutput as po on ec.PowerOutputId=po.PowerOutputId
left join CylinderHeadType as cht on ec.CylinderHeadTypeId=cht.CylinderHeadTypeId
left join IgnitionSystemType as ist on ec.IgnitionSystemTypeId=ist.IgnitionSystemTypeId
left join EngineMfr as MforEn on ec.EngineMfrId=MforEn.EngineMfrID

left join VehicleToAppearanceConfig as vac on v.VehicleID=vac.VehicleID
left join BodyStyleConfig as bsc on vac.BodyStyleConfigID=bsc.BodyStyleConfigID
left join OurBodyStyleConfig as obsc on obsc.OurBodyStyleConfigID = vac.OurBodyStyleConfigID
LEFT join OurBodyType as obt on obt.OurBodyTypeID = obsc.OurBodyTypeID
left join MfrBodyCode as mfr on vac.mfrbodycodeID=mfr.mfrbodycodeID
left join WheelBase as wb on vac.WheelBaseId=wb.WheelBaseId
left join BodyType as bt on bsc.BodyTypeID=bt.BodyTypeID
left join BedConfig as bc on vac.BedConfigID=bc.BedConfigID
left join BedType as bat on bc.BedTypeID=bat.BedTypeID
left join BedLength as bl on bc.BedLengthID=bl.BedLengthID
left join BodyNumDoors as bnd on bsc.bodynumdoorsID=bnd.bodynumdoorsID

left join VehicleToTransmission as vtt on v.VehicleId=vtt.VehicleId
left join Transmission as t on vtt.TransmissionId=t.TransmissionId
left join TransmissionBase as tb on t.TransmissionBaseId=tb.TransmissionBaseId
left join TransmissionType as tt on tb.TransmissionTypeId=tt.TransmissionTypeId
left join TransmissionControlType as tct on tb.TransmissionControlTypeId=tct.TransmissionControlTypeId
left join TransmissionNumSpeeds as tns on tb.TransmissionNumSpeedsId=tns.TransmissionNumSpeedsId
left join TransmissionMfrCode as tmc on t.TransmissionMfrCodeId=tmc.TransmissionMfrCodeId
left join TransmissionMfr as MforTr on t.TransmissionMfrId=MforTr.TransmissionMfrID

left join VehicleToBrakeConfig as vtbc on v.VehicleId=vtbc.VehicleId
left join BrakeConfig as bg on vtbc.BrakeConfigId=bg.BrakeConfigId
left join BrakeSystem as bs on bg.BrakeSystemId=bs.BrakeSystemId
left join BrakeAbs as ba on bg.BrakeAbsId=ba.BrakeAbsId
left join RearBrakeType as BTforR on bg.RearBrakeTypeId=BTforR.RearBrakeTypeId
left join FrontBrakeType as BTforF on bg.FrontBrakeTypeId=BTforF.FrontBrakeTypeId

left join VehicleToDriveType as vtdt on vtdt.VehicleID = v.VehicleID
left join DriveType as dt on dt.DriveTypeID = vtdt.DriveTypeID

left join VehicleToSpringTypeConfig as vtspt on vtspt.VehicleID = v.VehicleID
left join SpringTypeConfig as sptc on sptc.SpringTypeConfigID = vtspt.SpringTypeConfigID
left join FrontSpringType as fspt on fspt.FrontSpringTypeID = sptc.FrontSpringTypeID
left join RearSpringType as rspt on rspt.RearSpringTypeID = sptc.RearSpringTypeID

left join VehicleToSteeringConfig as vtstc on vtstc.VehicleID = v.VehicleID
left join SteeringConfig as stc on stc.SteeringConfigID = vtstc.SteeringConfigID
left join SteeringType as stt on stt.SteeringTypeID = stc.SteeringTypeID
left join SteeringSystem as sts on sts.SteeringSystemID = stc.SteeringSystemID

left join VehicleToOurVehicle as vtov on vtov.VehicleID = v.VehicleID
left join OurVehicle as ov on ov.OurVehicleID = vtov.OurVehicleID
left join OurBaseVehicle as obv on obv.OurBaseVehicleID = ov.OurBaseVehicleID

left join ourmodel as omd on obv.ourmodelid = omd.ourmodelid
left join oursubmodel as osm on ov.oursubmodelid = osm.oursubmodelid
left join ourrealmodel as orm on ov.ourrealmodelid = orm.ourrealmodelid
left join ourbasesubmodel as obs on ov.ourbasesubmodelid = obs.ourbasesubmodelid
left join ourmake as om on obv.OurMakeID = om.ourmakeid
left join OurYear as oy on oy.OurYearID = obv.OurYearID

left join OurVehicleTypeSubtype ovtst on ovtst.OurVehicleTypeSubtypeID = obv.OurVehicleTypeSubtypeID
left join OurVehicleType ovt on ovt.OurVehicleTypeID = ovtst.OurVehicleTypeID
left join OurVehicleSubtype ovst on ovst.OurVehicleSubtypeID = ovtst.OurVehicleSubtypeID

--where ovt.OurVehicleTypeName is not null and ovst.OurVehicleSubtypeName is not null

--where   bv.yearid>1900  and   regionid  like ('%%')