Scribd
Upload
64 views13 pages

Engine Data Query Overview

The document contains several SQL queries that are selecting data from various database tables including the engines table, des_texts table, and country_designations table. The queries are joining these tables to retrieve engine data including codes, specifications, and description text values. The results are being selected with various filters including engine and model IDs.

Uploaded by

Damien Jorgensen
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
64 views13 pages

Engine Data Query Overview

The document contains several SQL queries that are selecting data from various database tables including the engines table, des_texts table, and country_designations table. The queries are joining these tables to retrieve engine data including codes, specifications, and description text values. The results are being selected with various filters including engine and model IDs.

Uploaded by

Damien Jorgensen
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

select * from v_typesbasic

where mfa_id = 511 and TYP_MOD_ID = 5114

select des_texts.TEX_TEXT, country_designations.CDS_TEX_ID


FROM des_texts
INNER JOIN country_designations ON des_texts.TEX_ID =
country_designations.CDS_TEX_ID
where country_designations.CDS_LNG_ID = 4

select des_texts.*, country_designations.*


FROM des_texts
INNER JOIN country_designations ON des_texts.TEX_ID =
country_designations.CDS_TEX_ID
where country_designations.CDS_LNG_ID = 4

select
DISTINCT
engines.ENG_ID,
engines.ENG_CODE,
engines.ENG_MFA_ID,
engines.ENG_PCON_START,
engines.ENG_PCON_END,
engines.ENG_KW_FROM,
engines.ENG_KW_UPTO,
engines.ENG_HP_FROM,
engines.ENG_HP_UPTO,
engines.ENG_VALVES,
engines.ENG_CYLINDERS,
engines.ENG_CCM_FROM,
engines.ENG_CCM_UPTO,
KV_DESIGN_TEXTS.TEX_TEXT KV_DESIGN_DES,
KV_FUEL_TYPE_TEXTS.TEX_TEXT KV_FUEL_TYPE_DES,
KV_FUEL_SUPPLY_TEXTS.TEX_TEXT KV_FUEL_SUPPLY_DES,
engines.ENG_DESCRIPTION, -- VARCHAR(90)
KV_ENGINE_TEXTS.TEX_TEXT KV_ENGINE_TEXTS_DES,
engines.ENG_KW_RPM_FROM, -- rpm
engines.ENG_KW_RPM_UPTO,
engines.ENG_COMPRESSION_FROM, -- NUMERIC(6,3)
engines.ENG_COMPRESSION_UPTO, -- NUMERIC(6,3)
engines.ENG_DRILLING, -- NUMERIC(6,3)
engines.ENG_EXTENSION, -- NUMERIC(6,3)
engines.ENG_CRANKSHAFT, -- SMALLINT
KV_CHARGE_TEXTS.TEX_TEXT KV_CHARGE_DES,
KV_CYLINDERS_TEXTS.TEX_TEXT KV_CYLINDERS_DES,
KV_CONTROL_TEXTS.TEX_TEXT KV_CONTROL_DES,
KV_VALVE_CONTROL_TEXTS.TEX_TEXT KV_VALVE_CONTROL_DES,
KV_COOLING_TEXTS.TEX_TEXT KV_COOLING_DES,
KV_USE_TEXTS.TEX_TEXT KV_USE_DES,
---
engines.ENG_LITRES_FROM, -- NUMERIC(6,3)
engines.ENG_LITRES_UPTO -- NUMERIC(6,3)
FROM engines
-- INNER JOIN TOF_MANUFACTURERS ON TOF_MANUFACTURERS.MFA_ID = engines.ENG_MFA_ID
-- INNER JOIN MODELS ON MODELS.MOD_MFA_ID = engines.ENG_MFA_ID
-- INNER JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = engines.ENG_KV_DESIGN_DES_ID
-- INNER JOIN DES_TEXTS ON DESIGNATIONS.DES_TEX_ID = DES_TEXTS.TEX_ID
-- DESCRIPTIONS:
LEFT JOIN DESIGNATIONS KV_DESIGN ON KV_DESIGN.DES_ID = engines.ENG_KV_DESIGN_DES_ID

LEFT JOIN DES_TEXTS KV_DESIGN_TEXTS ON KV_DESIGN.DES_TEX_ID =


KV_DESIGN_TEXTS.TEX_ID
LEFT JOIN DESIGNATIONS KV_FUEL_TYPE ON KV_FUEL_TYPE.DES_ID =
engines.ENG_KV_FUEL_TYPE_DES_ID
LEFT JOIN DES_TEXTS KV_FUEL_TYPE_TEXTS ON KV_FUEL_TYPE.DES_TEX_ID =
KV_FUEL_TYPE_TEXTS.TEX_ID
LEFT JOIN DESIGNATIONS KV_FUEL_SUPPLY ON KV_FUEL_SUPPLY.DES_ID =
engines.ENG_KV_FUEL_SUPPLY_DES_ID
LEFT JOIN DES_TEXTS KV_FUEL_SUPPLY_TEXTS ON KV_FUEL_SUPPLY.DES_TEX_ID =
KV_FUEL_SUPPLY_TEXTS.TEX_ID
LEFT JOIN DESIGNATIONS KV_ENGINE ON KV_ENGINE.DES_ID = engines.ENG_KV_ENGINE_DES_ID
LEFT JOIN DES_TEXTS KV_ENGINE_TEXTS ON KV_ENGINE.DES_TEX_ID =
KV_ENGINE_TEXTS.TEX_ID
--engines.ENG_KV_CHARGE_DES_ID
LEFT JOIN DESIGNATIONS KV_CHARGE ON KV_CHARGE.DES_ID = engines.ENG_KV_CHARGE_DES_ID

LEFT JOIN DES_TEXTS KV_CHARGE_TEXTS ON KV_CHARGE.DES_TEX_ID =


KV_CHARGE_TEXTS.TEX_ID
-- engines.ENG_KV_CYLINDERS_DES_ID
LEFT JOIN DESIGNATIONS KV_CYLINDERS ON KV_CYLINDERS.DES_ID =
engines.ENG_KV_CYLINDERS_DES_ID
LEFT JOIN DES_TEXTS KV_CYLINDERS_TEXTS ON KV_CYLINDERS.DES_TEX_ID =
KV_CYLINDERS_TEXTS.TEX_ID
-- engines.ENG_KV_CONTROL_DES_ID
LEFT JOIN DESIGNATIONS KV_CONTROL ON KV_CONTROL.DES_ID =
engines.ENG_KV_CONTROL_DES_ID
LEFT JOIN DES_TEXTS KV_CONTROL_TEXTS ON KV_CONTROL.DES_TEX_ID =
KV_CONTROL_TEXTS.TEX_ID
-- engines.ENG_KV_VALVE_CONTROL_DES_ID
LEFT JOIN DESIGNATIONS KV_VALVE_CONTROL ON KV_VALVE_CONTROL.DES_ID =
engines.ENG_KV_VALVE_CONTROL_DES_ID
LEFT JOIN DES_TEXTS KV_VALVE_CONTROL_TEXTS ON KV_VALVE_CONTROL.DES_TEX_ID =
KV_VALVE_CONTROL_TEXTS.TEX_ID
-- engines.ENG_KV_COOLING_DES_ID
LEFT JOIN DESIGNATIONS KV_COOLING ON KV_COOLING.DES_ID =
engines.ENG_KV_COOLING_DES_ID
LEFT JOIN DES_TEXTS KV_COOLING_TEXTS ON KV_COOLING.DES_TEX_ID =
KV_COOLING_TEXTS.TEX_ID
-- engines.ENG_KV_USE_DES_ID
LEFT JOIN DESIGNATIONS KV_USE ON KV_USE.DES_ID = engines.ENG_KV_USE_DES_ID
LEFT JOIN DES_TEXTS KV_USE_TEXTS ON KV_USE.DES_TEX_ID = KV_USE_TEXTS.TEX_ID
DISTINCT TOF_ENGINES.ENG_ID,
TOF_MODELS.MOD_ID,
TOF_ENGINES.ENG_CODE,
TOF_ENGINES.ENG_MFA_ID,
TOF_ENGINES.ENG_PCON_START,
TOF_ENGINES.ENG_PCON_END,
TOF_ENGINES.ENG_KW_FROM,
TOF_ENGINES.ENG_KW_UPTO,
TOF_ENGINES.ENG_HP_FROM,
TOF_ENGINES.ENG_HP_UPTO,
TOF_ENGINES.ENG_VALVES,
TOF_ENGINES.ENG_CYLINDERS,
TOF_ENGINES.ENG_CCM_FROM,
TOF_ENGINES.ENG_CCM_UPTO,
KV_DESIGN_TEXTS.TEX_TEXT KV_DESIGN_DES,
KV_FUEL_TYPE_TEXTS.TEX_TEXT KV_FUEL_TYPE_DES,
KV_FUEL_SUPPLY_TEXTS.TEX_TEXT KV_FUEL_SUPPLY_DES,
TOF_ENGINES.ENG_DESCRIPTION, -- VARCHAR(90)
KV_ENGINE_TEXTS.TEX_TEXT KV_ENGINE_TEXTS_DES,
TOF_ENGINES.ENG_KW_RPM_FROM, -- rpm
TOF_ENGINES.ENG_KW_RPM_UPTO,
TOF_ENGINES.ENG_COMPRESSION_FROM, -- NUMERIC(6,3)
TOF_ENGINES.ENG_COMPRESSION_UPTO, -- NUMERIC(6,3)
TOF_ENGINES.ENG_DRILLING, -- NUMERIC(6,3)
TOF_ENGINES.ENG_EXTENSION, -- NUMERIC(6,3)
TOF_ENGINES.ENG_CRANKSHAFT, -- SMALLINT
KV_CHARGE_TEXTS.TEX_TEXT KV_CHARGE_DES,
KV_CYLINDERS_TEXTS.TEX_TEXT KV_CYLINDERS_DES,
KV_CONTROL_TEXTS.TEX_TEXT KV_CONTROL_DES,
KV_VALVE_CONTROL_TEXTS.TEX_TEXT KV_VALVE_CONTROL_DES,
KV_COOLING_TEXTS.TEX_TEXT KV_COOLING_DES,
KV_USE_TEXTS.TEX_TEXT KV_USE_DES,
---
TOF_ENGINES.ENG_LITRES_FROM, -- NUMERIC(6,3)
TOF_ENGINES.ENG_LITRES_UPTO -- NUMERIC(6,3)
FROM TOF_ENGINES
-- INNER JOIN TOF_MANUFACTURERS ON TOF_MANUFACTURERS.MFA_ID =
TOF_ENGINES.ENG_MFA_ID
INNER JOIN TOF_MODELS ON TOF_MODELS.MOD_MFA_ID = TOF_ENGINES.ENG_MFA_ID
-- INNER JOIN TOF_DESIGNATIONS ON TOF_DESIGNATIONS.DES_ID =
TOF_ENGINES.ENG_KV_DESIGN_DES_ID
-- INNER JOIN TOF_DES_TEXTS ON TOF_DESIGNATIONS.DES_TEX_ID = TOF_DES_TEXTS.TEX_ID
-- DESCRIPTIONS:
LEFT JOIN TOF_DESIGNATIONS KV_DESIGN ON KV_DESIGN.DES_ID =
TOF_ENGINES.ENG_KV_DESIGN_DES_ID AND KV_DESIGN.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_DESIGN_TEXTS ON KV_DESIGN.DES_TEX_ID =
KV_DESIGN_TEXTS.TEX_ID
LEFT JOIN TOF_DESIGNATIONS KV_FUEL_TYPE ON KV_FUEL_TYPE.DES_ID =
TOF_ENGINES.ENG_KV_FUEL_TYPE_DES_ID AND KV_FUEL_TYPE.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_FUEL_TYPE_TEXTS ON KV_FUEL_TYPE.DES_TEX_ID =
KV_FUEL_TYPE_TEXTS.TEX_ID
LEFT JOIN TOF_DESIGNATIONS KV_FUEL_SUPPLY ON KV_FUEL_SUPPLY.DES_ID =
TOF_ENGINES.ENG_KV_FUEL_SUPPLY_DES_ID AND KV_FUEL_SUPPLY.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_FUEL_SUPPLY_TEXTS ON KV_FUEL_SUPPLY.DES_TEX_ID =
KV_FUEL_SUPPLY_TEXTS.TEX_ID
LEFT JOIN TOF_DESIGNATIONS KV_ENGINE ON KV_ENGINE.DES_ID =
TOF_ENGINES.ENG_KV_ENGINE_DES_ID AND KV_ENGINE.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_ENGINE_TEXTS ON KV_ENGINE.DES_TEX_ID =
KV_ENGINE_TEXTS.TEX_ID
--TOF_ENGINES.ENG_KV_CHARGE_DES_ID
LEFT JOIN TOF_DESIGNATIONS KV_CHARGE ON KV_CHARGE.DES_ID =
TOF_ENGINES.ENG_KV_CHARGE_DES_ID AND KV_CHARGE.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_CHARGE_TEXTS ON KV_CHARGE.DES_TEX_ID =
KV_CHARGE_TEXTS.TEX_ID
-- TOF_ENGINES.ENG_KV_CYLINDERS_DES_ID
LEFT JOIN TOF_DESIGNATIONS KV_CYLINDERS ON KV_CYLINDERS.DES_ID =
TOF_ENGINES.ENG_KV_CYLINDERS_DES_ID AND KV_CYLINDERS.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_CYLINDERS_TEXTS ON KV_CYLINDERS.DES_TEX_ID =
KV_CYLINDERS_TEXTS.TEX_ID
-- TOF_ENGINES.ENG_KV_CONTROL_DES_ID
LEFT JOIN TOF_DESIGNATIONS KV_CONTROL ON KV_CONTROL.DES_ID =
TOF_ENGINES.ENG_KV_CONTROL_DES_ID AND KV_CONTROL.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_CONTROL_TEXTS ON KV_CONTROL.DES_TEX_ID =
KV_CONTROL_TEXTS.TEX_ID
-- TOF_ENGINES.ENG_KV_VALVE_CONTROL_DES_ID
LEFT JOIN TOF_DESIGNATIONS KV_VALVE_CONTROL ON KV_VALVE_CONTROL.DES_ID =
TOF_ENGINES.ENG_KV_VALVE_CONTROL_DES_ID AND KV_VALVE_CONTROL.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_VALVE_CONTROL_TEXTS ON KV_VALVE_CONTROL.DES_TEX_ID =
KV_VALVE_CONTROL_TEXTS.TEX_ID
-- TOF_ENGINES.ENG_KV_COOLING_DES_ID
LEFT JOIN TOF_DESIGNATIONS KV_COOLING ON KV_COOLING.DES_ID =
TOF_ENGINES.ENG_KV_COOLING_DES_ID AND KV_COOLING.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_COOLING_TEXTS ON KV_COOLING.DES_TEX_ID =
KV_COOLING_TEXTS.TEX_ID
-- TOF_ENGINES.ENG_KV_USE_DES_ID
LEFT JOIN TOF_DESIGNATIONS KV_USE ON KV_USE.DES_ID = TOF_ENGINES.ENG_KV_USE_DES_ID
AND KV_USE.DES_LNG_ID = 16
LEFT JOIN TOF_DES_TEXTS KV_USE_TEXTS ON KV_USE.DES_TEX_ID = KV_USE_TEXTS.TEX_ID
WHERE
(ENG_CTM SUBRANGE (16 CAST INTEGER) = 1 OR ENG_LA_CTM SUBRANGE (16 CAST INTEGER) =
1) AND MOD_ID = {0}";
===================================================================================
=============================
===================================================================================
=============================
good one
v_engines

select
DISTINCT
engines.ENG_ID,
engines.ENG_CODE,
engines.ENG_MFA_ID,
engines.ENG_PCON_START,
engines.ENG_PCON_END,
engines.ENG_KW_FROM,
engines.ENG_KW_UPTO,
engines.ENG_HP_FROM,
engines.ENG_HP_UPTO,
engines.ENG_VALVES,
engines.ENG_CYLINDERS,
engines.ENG_CCM_FROM,
engines.ENG_CCM_UPTO,
engines.ENG_LITRES_FROM, -- NUMERIC(6,3)
engines.ENG_LITRES_UPTO, -- NUMERIC(6,3)
engines.ENG_DESCRIPTION, -- VARCHAR(90)
engines.ENG_KW_RPM_FROM, -- rpm
engines.ENG_KW_RPM_UPTO,
engines.ENG_COMPRESSION_FROM, -- NUMERIC(6,3)
engines.ENG_COMPRESSION_UPTO, -- NUMERIC(6,3)
engines.ENG_DRILLING, -- NUMERIC(6,3)
engines.ENG_EXTENSION, -- NUMERIC(6,3)
engines.ENG_CRANKSHAFT, -- SMALLINT

KV_ENGINE_TEXTS.TEX_TEXT KV_ENGINE_TEXTS_DES,
KV_DESIGN_TEXTS.TEX_TEXT KV_DESIGN_DES,
KV_FUEL_TYPE_TEXTS.TEX_TEXT KV_FUEL_TYPE_DES,
KV_FUEL_SUPPLY_TEXTS.TEX_TEXT KV_FUEL_SUPPLY_DES,
/*KV_CHARGE_TEXTS.TEX_TEXT KV_CHARGE_DES,*/
KV_CYLINDERS_TEXTS.TEX_TEXT KV_CYLINDERS_DES,
KV_CONTROL_TEXTS.TEX_TEXT KV_CONTROL_DES,
KV_VALVE_CONTROL_TEXTS.TEX_TEXT KV_VALVE_CONTROL_DES,
KV_COOLING_TEXTS.TEX_TEXT KV_COOLING_DES,
KV_USE_TEXTS.TEX_TEXT KV_USE_DES

FROM engines
-- INNER JOIN TOF_MANUFACTURERS ON TOF_MANUFACTURERS.MFA_ID = engines.ENG_MFA_ID
-- INNER JOIN MODELS ON MODELS.MOD_MFA_ID = engines.ENG_MFA_ID
-- INNER JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = engines.ENG_KV_DESIGN_DES_ID
-- INNER JOIN DES_TEXTS ON DESIGNATIONS.DES_TEX_ID = DES_TEXTS.TEX_ID
-- DESCRIPTIONS:

LEFT JOIN DESIGNATIONS KV_DESIGN ON KV_DESIGN.DES_ID = engines.ENG_KV_DESIGN_DES_ID

LEFT JOIN DES_TEXTS KV_DESIGN_TEXTS ON KV_DESIGN.DES_TEX_ID =


KV_DESIGN_TEXTS.TEX_ID
LEFT JOIN DESIGNATIONS KV_FUEL_TYPE ON KV_FUEL_TYPE.DES_ID =
engines.ENG_KV_FUEL_TYPE_DES_ID
LEFT JOIN DES_TEXTS KV_FUEL_TYPE_TEXTS ON KV_FUEL_TYPE.DES_TEX_ID =
KV_FUEL_TYPE_TEXTS.TEX_ID
LEFT JOIN DESIGNATIONS KV_FUEL_SUPPLY ON KV_FUEL_SUPPLY.DES_ID =
engines.ENG_KV_FUEL_SUPPLY_DES_ID
LEFT JOIN DES_TEXTS KV_FUEL_SUPPLY_TEXTS ON KV_FUEL_SUPPLY.DES_TEX_ID =
KV_FUEL_SUPPLY_TEXTS.TEX_ID
LEFT JOIN DESIGNATIONS KV_ENGINE ON KV_ENGINE.DES_ID = engines.ENG_KV_ENGINE_DES_ID
LEFT JOIN DES_TEXTS KV_ENGINE_TEXTS ON KV_ENGINE.DES_TEX_ID =
KV_ENGINE_TEXTS.TEX_ID

/*
--engines.ENG_KV_CHARGE_DES_ID
LEFT JOIN DESIGNATIONS KV_CHARGE ON KV_CHARGE.DES_ID = engines.ENG_KV_CHARGE_DES_ID

LEFT JOIN DES_TEXTS KV_CHARGE_TEXTS ON KV_CHARGE.DES_TEX_ID =


KV_CHARGE_TEXTS.TEX_ID
*/

-- engines.ENG_KV_CYLINDERS_DES_ID
LEFT JOIN DESIGNATIONS KV_CYLINDERS ON KV_CYLINDERS.DES_ID =
engines.ENG_KV_CYLINDERS_DES_ID
LEFT JOIN DES_TEXTS KV_CYLINDERS_TEXTS ON KV_CYLINDERS.DES_TEX_ID =
KV_CYLINDERS_TEXTS.TEX_ID
-- engines.ENG_KV_CONTROL_DES_ID

LEFT JOIN DESIGNATIONS KV_CONTROL ON KV_CONTROL.DES_ID =


engines.ENG_KV_CONTROL_DES_ID
LEFT JOIN DES_TEXTS KV_CONTROL_TEXTS ON KV_CONTROL.DES_TEX_ID =
KV_CONTROL_TEXTS.TEX_ID
-- engines.ENG_KV_VALVE_CONTROL_DES_ID

LEFT JOIN DESIGNATIONS KV_VALVE_CONTROL ON KV_VALVE_CONTROL.DES_ID =


engines.ENG_KV_VALVE_CONTROL_DES_ID
LEFT JOIN DES_TEXTS KV_VALVE_CONTROL_TEXTS ON KV_VALVE_CONTROL.DES_TEX_ID =
KV_VALVE_CONTROL_TEXTS.TEX_ID
-- engines.ENG_KV_COOLING_DES_ID
LEFT JOIN DESIGNATIONS KV_COOLING ON KV_COOLING.DES_ID =
engines.ENG_KV_COOLING_DES_ID
LEFT JOIN DES_TEXTS KV_COOLING_TEXTS ON KV_COOLING.DES_TEX_ID =
KV_COOLING_TEXTS.TEX_ID
-- engines.ENG_KV_USE_DES_ID
LEFT JOIN DESIGNATIONS KV_USE ON KV_USE.DES_ID = engines.ENG_KV_USE_DES_ID
LEFT JOIN DES_TEXTS KV_USE_TEXTS ON KV_USE.DES_TEX_ID = KV_USE_TEXTS.TEX_ID
where ENG_MFA_ID = 511

===================================================================================
=============================
===================================================================================
=============================
===================================================================================
=============================

v_engine_bymodels
doesnt work

select
DISTINCT
v_engines.*,
MODELS.MOD_ID

FROM v_engines

INNER JOIN MODELS ON MODELS.MOD_MFA_ID = v_engines.ENG_MFA_ID

===================================================================================
=============================
===================================================================================
=============================
===================================================================================
=============================
v_types

SELECT TYP_ID
, MFA_BRAND
, DES_TEXTS7.TEX_TEXT AS MOD_CDS_TEXT
, DES_TEXTS.TEX_TEXT AS TYP_CDS_TEXT
, TYP_PCON_START, TYP_PCON_END
, TYP_CCM, TYP_KW_FROM
, TYP_KW_UPTO
, TYP_HP_FROM
, TYP_HP_UPTO
, TYP_CYLINDERS
, v_ENGINES.ENG_CODE
, DES_TEXTS2.TEX_TEXT AS TYP_ENGINE_DES_TEXT
, DES_TEXTS3.TEX_TEXT AS TYP_FUEL_DES_TEXT
, IFNULL(DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS TYP_BODY_DES_TEXT
, DES_TEXTS6.TEX_TEXT AS TYP_AXLE_DES_TEXT
, TYP_MAX_WEIGHT

FROM
TYPES
INNER JOIN MODELS ON MOD_ID = TYP_MOD_ID
INNER JOIN MANUFACTURERS ON MFA_ID = MOD_MFA_ID
INNER JOIN COUNTRY_DESIGNATIONS AS COUNTRY_DESIGNATIONS2 ON
COUNTRY_DESIGNATIONS2.CDS_ID = MOD_CDS_ID AND COUNTRY_DESIGNATIONS2.CDS_LNG_ID = 4
INNER JOIN DES_TEXTS AS DES_TEXTS7 ON DES_TEXTS7.TEX_ID =
COUNTRY_DESIGNATIONS2.CDS_TEX_ID
INNER JOIN COUNTRY_DESIGNATIONS ON COUNTRY_DESIGNATIONS.CDS_ID = TYP_CDS_ID AND
COUNTRY_DESIGNATIONS.CDS_LNG_ID = 4
INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = COUNTRY_DESIGNATIONS.CDS_TEX_ID
LEFT JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = TYP_KV_ENGINE_DES_ID AND
DESIGNATIONS.DES_LNG_ID = 4
LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS.DES_TEX_ID
LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID =
TYP_KV_FUEL_DES_ID AND DESIGNATIONS2.DES_LNG_ID = 4
LEFT JOIN DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS2.DES_TEX_ID
LEFT JOIN LINK_TYP_ENG ON LTE_TYP_ID = TYP_ID
LEFT JOIN v_ENGINES ON ENG_ID = LTE_ENG_ID

LEFT JOIN DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID =


TYP_KV_BODY_DES_ID AND DESIGNATIONS3.DES_LNG_ID = 4
LEFT JOIN DES_TEXTS AS DES_TEXTS4 ON DES_TEXTS4.TEX_ID = DESIGNATIONS3.DES_TEX_ID
LEFT JOIN DESIGNATIONS AS DESIGNATIONS4 ON DESIGNATIONS4.DES_ID =
TYP_KV_MODEL_DES_ID AND DESIGNATIONS4.DES_LNG_ID = 4
LEFT JOIN DES_TEXTS AS DES_TEXTS5 ON DES_TEXTS5.TEX_ID = DESIGNATIONS4.DES_TEX_ID
LEFT JOIN DESIGNATIONS AS DESIGNATIONS5 ON DESIGNATIONS5.DES_ID =
TYP_KV_AXLE_DES_ID AND DESIGNATIONS5.DES_LNG_ID = 4
LEFT JOIN DES_TEXTS AS DES_TEXTS6 ON DES_TEXTS6.TEX_ID = DESIGNATIONS5.DES_TEX_ID

ORDER BY MFA_BRAND, MOD_CDS_TEXT, TYP_CDS_TEXT, TYP_PCON_START,


TYP_CCM

the use WHERE TYP_MOD_ID = 286 to modify the vehicles types


===================================================================================
=============================
===================================================================================
=============================
===================================================================================
=============================

to use to get a list of model types from modid i.e. the model variants are modid
listed on [Link]

SET @MOD_ID = 5114;

SELECT TYP_ID, MFA_BRAND, DES_TEXTS7.TEX_TEXT AS MOD_CDS_TEXT,


DES_TEXTS.TEX_TEXT AS TYP_CDS_TEXT, TYP_PCON_START, TYP_PCON_END,
TYP_CCM, TYP_KW_FROM, TYP_KW_UPTO, TYP_HP_FROM, TYP_HP_UPTO,
TYP_CYLINDERS, ENGINES.ENG_CODE, DES_TEXTS2.TEX_TEXT AS
TYP_ENGINE_DES_TEXT, DES_TEXTS3.TEX_TEXT AS TYP_FUEL_DES_TEXT,
IFNULL(DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS TYP_BODY_DES_TEXT,
DES_TEXTS6.TEX_TEXT AS TYP_AXLE_DES_TEXT, TYP_MAX_WEIGHT
FROM TYPES
INNER JOIN MODELS ON MOD_ID = TYP_MOD_ID
INNER JOIN MANUFACTURERS ON MFA_ID = MOD_MFA_ID
INNER JOIN COUNTRY_DESIGNATIONS AS COUNTRY_DESIGNATIONS2 ON
COUNTRY_DESIGNATIONS2.CDS_ID = MOD_CDS_ID
INNER JOIN DES_TEXTS AS DES_TEXTS7 ON DES_TEXTS7.TEX_ID =
COUNTRY_DESIGNATIONS2.CDS_TEX_ID
INNER JOIN COUNTRY_DESIGNATIONS ON COUNTRY_DESIGNATIONS.CDS_ID = TYP_CDS_ID
INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = COUNTRY_DESIGNATIONS.CDS_TEX_ID
LEFT JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = TYP_KV_ENGINE_DES_ID
LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS.DES_TEX_ID
LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID =
TYP_KV_FUEL_DES_ID
LEFT JOIN DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS2.DES_TEX_ID
LEFT JOIN LINK_TYP_ENG ON LTE_TYP_ID = TYP_ID
LEFT JOIN ENGINES ON ENG_ID = LTE_ENG_ID
LEFT JOIN DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID =
TYP_KV_BODY_DES_ID
LEFT JOIN DES_TEXTS AS DES_TEXTS4 ON DES_TEXTS4.TEX_ID = DESIGNATIONS3.DES_TEX_ID
LEFT JOIN DESIGNATIONS AS DESIGNATIONS4 ON DESIGNATIONS4.DES_ID =
TYP_KV_MODEL_DES_ID
LEFT JOIN DES_TEXTS AS DES_TEXTS5 ON DES_TEXTS5.TEX_ID = DESIGNATIONS4.DES_TEX_ID
LEFT JOIN DESIGNATIONS AS DESIGNATIONS5 ON DESIGNATIONS5.DES_ID =
TYP_KV_AXLE_DES_ID
LEFT JOIN DES_TEXTS AS DES_TEXTS6 ON DES_TEXTS6.TEX_ID = DESIGNATIONS5.DES_TEX_ID
WHERE TYP_MOD_ID = @MOD_ID
ORDER BY MFA_BRAND, MOD_CDS_TEXT, TYP_CDS_TEXT,
TYP_PCON_START, TYP_CCM
LIMIT 100;

-----------------------------------------------------------------------------------
-----------------------------

SELECT

engines.* ,

DES_TEXTSa.tex_text as TYP_MMT_TEXT,

DES_TEXTS7.TEX_TEXT AS MOD_CDS_TEXT,
DES_TEXTS.TEX_TEXT AS TYP_CDS_TEXT,

DES_TEXTS2.TEX_TEXT AS TYP_ENGINE_DES_TEXT,
DES_TEXTS3.TEX_TEXT AS TYP_FUEL_DES_TEXT,
IFNULL(DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS TYP_BODY_DES_TEXT ,

TYPES.*
FROM

TYPES

INNER JOIN MODELS ON MOD_ID = TYP_MOD_ID

INNER JOIN MANUFACTURERS ON MFA_ID = MOD_MFA_ID

INNER JOIN COUNTRY_DESIGNATIONS AS COUNTRY_DESIGNATIONSa ON


COUNTRY_DESIGNATIONSa.CDS_ID = TYPES.TYP_MMT_CDS_ID
INNER JOIN DES_TEXTS as DES_TEXTSa ON COUNTRY_DESIGNATIONSa.CDS_TEX_ID =
DES_TEXTSa.TEX_ID

INNER JOIN COUNTRY_DESIGNATIONS AS COUNTRY_DESIGNATIONS2 ON


COUNTRY_DESIGNATIONS2.CDS_ID = MOD_CDS_ID
INNER JOIN DES_TEXTS AS DES_TEXTS7 ON DES_TEXTS7.TEX_ID =
COUNTRY_DESIGNATIONS2.CDS_TEX_ID
INNER JOIN COUNTRY_DESIGNATIONS ON COUNTRY_DESIGNATIONS.CDS_ID = TYP_CDS_ID
INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = COUNTRY_DESIGNATIONS.CDS_TEX_ID

LEFT JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = TYP_KV_ENGINE_DES_ID


LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS.DES_TEX_ID

LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID =


TYP_KV_FUEL_DES_ID
LEFT JOIN DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS2.DES_TEX_ID

LEFT JOIN LINK_TYP_ENG ON LTE_TYP_ID = TYP_ID


LEFT JOIN v_ENGINES as engines ON ENG_ID = LTE_ENG_ID

LEFT JOIN DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID =


TYP_KV_BODY_DES_ID
LEFT JOIN DES_TEXTS AS DES_TEXTS4 ON DES_TEXTS4.TEX_ID = DESIGNATIONS3.DES_TEX_ID
LEFT JOIN DESIGNATIONS AS DESIGNATIONS4 ON DESIGNATIONS4.DES_ID =
TYP_KV_MODEL_DES_ID
LEFT JOIN DES_TEXTS AS DES_TEXTS5 ON DES_TEXTS5.TEX_ID = DESIGNATIONS4.DES_TEX_ID

WHERE TYP_MOD_ID = 5114

and replace(DES_TEXTS.TEX_TEXT,' ','_') = '116_i'


===================================================================================
=============================
===================================================================================
=============================
===================================================================================
=============================
shows years a model of car is available

SET @MOD_ID = 5114; /* MERCEDES-BENZ NG */

SELECT

TYP_PCON_START
, TYP_PCON_END

FROM TYPES
INNER JOIN MODELS ON MOD_ID = TYP_MOD_ID
INNER JOIN MANUFACTURERS ON MFA_ID = MOD_MFA_ID
INNER JOIN COUNTRY_DESIGNATIONS AS COUNTRY_DESIGNATIONS2 ON
COUNTRY_DESIGNATIONS2.CDS_ID = MOD_CDS_ID
INNER JOIN DES_TEXTS AS DES_TEXTS7 ON DES_TEXTS7.TEX_ID =
COUNTRY_DESIGNATIONS2.CDS_TEX_ID
INNER JOIN COUNTRY_DESIGNATIONS ON COUNTRY_DESIGNATIONS.CDS_ID = TYP_CDS_ID
INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = COUNTRY_DESIGNATIONS.CDS_TEX_ID
LEFT JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = TYP_KV_ENGINE_DES_ID
LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS.DES_TEX_ID
LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID =
TYP_KV_FUEL_DES_ID
LEFT JOIN DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS2.DES_TEX_ID
LEFT JOIN LINK_TYP_ENG ON LTE_TYP_ID = TYP_ID
LEFT JOIN ENGINES ON ENG_ID = LTE_ENG_ID
LEFT JOIN DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID =
TYP_KV_BODY_DES_ID
LEFT JOIN DES_TEXTS AS DES_TEXTS4 ON DES_TEXTS4.TEX_ID = DESIGNATIONS3.DES_TEX_ID
LEFT JOIN DESIGNATIONS AS DESIGNATIONS4 ON DESIGNATIONS4.DES_ID =
TYP_KV_MODEL_DES_ID
LEFT JOIN DES_TEXTS AS DES_TEXTS5 ON DES_TEXTS5.TEX_ID = DESIGNATIONS4.DES_TEX_ID
LEFT JOIN DESIGNATIONS AS DESIGNATIONS5 ON DESIGNATIONS5.DES_ID =
TYP_KV_AXLE_DES_ID
LEFT JOIN DES_TEXTS AS DES_TEXTS6 ON DES_TEXTS6.TEX_ID = DESIGNATIONS5.DES_TEX_ID
WHERE TYP_MOD_ID = @MOD_ID

LIMIT 100;
===================================================================================
=============================
===================================================================================
=============================
===================================================================================
=============================
v_types 2 -ive modified it so that it shows you bmw models in a more useuable way

SELECT distinct

MFA_BRAND
, DES_TEXTS7.TEX_TEXT AS MOD_CDS_TEXT
, DES_TEXTS.TEX_TEXT AS TYP_CDS_TEXT
, TYP_MOD_ID
, MANUFACTURERS.MFA_ID
, DES_TEXTS3.TEX_TEXT AS TYP_FUEL_DES_TEXT
, IFNULL(DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS TYP_BODY_DES_TEXT

FROM
TYPES
INNER JOIN MODELS ON MOD_ID = TYP_MOD_ID
INNER JOIN MANUFACTURERS ON MFA_ID = MOD_MFA_ID
INNER JOIN COUNTRY_DESIGNATIONS AS COUNTRY_DESIGNATIONS2 ON
COUNTRY_DESIGNATIONS2.CDS_ID = MOD_CDS_ID AND COUNTRY_DESIGNATIONS2.CDS_LNG_ID = 4
INNER JOIN DES_TEXTS AS DES_TEXTS7 ON DES_TEXTS7.TEX_ID =
COUNTRY_DESIGNATIONS2.CDS_TEX_ID
INNER JOIN COUNTRY_DESIGNATIONS ON COUNTRY_DESIGNATIONS.CDS_ID = TYP_CDS_ID AND
COUNTRY_DESIGNATIONS.CDS_LNG_ID = 4
INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = COUNTRY_DESIGNATIONS.CDS_TEX_ID

LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID =


TYP_KV_FUEL_DES_ID AND DESIGNATIONS2.DES_LNG_ID = 4
LEFT JOIN DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS2.DES_TEX_ID
LEFT JOIN LINK_TYP_ENG ON LTE_TYP_ID = TYP_ID

LEFT JOIN DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID =


TYP_KV_BODY_DES_ID AND DESIGNATIONS3.DES_LNG_ID = 4
LEFT JOIN DES_TEXTS AS DES_TEXTS4 ON DES_TEXTS4.TEX_ID = DESIGNATIONS3.DES_TEX_ID

LEFT JOIN DESIGNATIONS AS DESIGNATIONS4 ON DESIGNATIONS4.DES_ID =


TYP_KV_MODEL_DES_ID AND DESIGNATIONS4.DES_LNG_ID = 4
LEFT JOIN DES_TEXTS AS DES_TEXTS5 ON DES_TEXTS5.TEX_ID = DESIGNATIONS4.DES_TEX_ID

where TYP_MOD_ID = 286

the use WHERE TYP_MOD_ID = 286 to modify the vehicles types


===================================================================================
=============================
===================================================================================
=============================
===================================================================================
=============================

You might also like