AUDACON Автомобили

База данных Audacon содержит свою внутренню базу автомобилей, связанную с автомобилями базы данных TecDoc. Таким образом можно использовать базы самостоятельно или совместно.

Получения типа автомобиля, исользуя TYP_ID базы TecDoc

TypeID
Идентификатор типа автомобиля
SET @TYP_ID = 25456; /* AUDI A4 (8K2, B8) 1.8 TFSI 2008-НВ */

SELECT IFNULL(TB_TreeExtTcdReference.TypeID, TB_TreeType.TypeID) TypeID
FROM TB_TreeRange
INNER JOIN TB_TreeType ON TB_TreeRange.RangeID = TB_TreeType.RangeID
LEFT OUTER JOIN TB_TreeExtTcdReference ON TB_TreeType.TypeID = TB_TreeExtTcdReference.TypeID
WHERE ((TB_TreeRange.ClassID = 1) AND (TB_TreeType.TcdType = @TYP_ID)) OR ((TB_TreeExtTcdReference.TcdTypeID = @TYP_ID) AND (TB_TreeExtTcdReference.ClassID = 1))


Вывод всех марок автомобилей (производителей) TNX @borodatych

Изначально в БД Audacon есть своя база данных автомобилей. Но она очень скудна в части описаний - отсутствуют годы выпуска, форма кузова, код двигателя и прочая информация. Для решения этого вопроса было принято решение объединить таблицы Audacon и Tecdoc в одной базе данных и формировать выгрузку автомобилей, используя их соединение.

SELECT mnf.MFA_ID, mnf.MFA_BRAND
FROM tb_treemaketext AS brand
JOIN tb_treemake AS filter ON filter.MAKEID = brand.MAKEID
JOIN tof_manufacturers AS mnf ON mnf.MFA_MF_NR = filter.TCDMAKE
WHERE 1=1
AND brand.COUNTRYID = 0
AND filter.ISCOMPONENT = 0
ORDER BY brand.MAKENAME;


Вывод списка моделей по заданной марке автомобиля (MFA_ID) TNX @borodatych


SET @MFA_ID = 504;
SET @LNG_ID = 16;

SELECT
MOD_ID, TEX_TEXT AS MOD_CDS_TEXT, MOD_PCON_START, MOD_PCON_END
FROM tof_models AS models
INNER JOIN tof_country_designations AS country ON country.CDS_ID = models.MOD_CDS_ID
INNER JOIN tof_des_texts AS descript ON descript.TEX_ID = country.CDS_TEX_ID
INNER JOIN tb_treerange AS RT ON RT.TCDRANGE = MOD_ID
INNER JOIN tb_treemake AS MT ON MT.MAKEID = RT.MAKEID
INNER JOIN tof_manufacturers AS brand ON brand.MFA_MF_NR = MT.TCDMAKE AND brand.MFA_ID = models.MOD_MFA_ID
WHERE 1=1
AND models.MOD_MFA_ID = @MFA_ID
AND country.CDS_LNG_ID = @LNG_ID
GROUP BY MOD_ID
ORDER BY descript.TEX_TEXT;


Вывод списка типов по заданной модели (MFA_ID): TNX @borodatych


SET @MOD_ID = 3908;
SET @LNG_ID = 16;

SELECT
SELECT TYP_ID, TT.TYPEID, MFA_BRAND,
DES_TEXTS7.TEX_TEXT AS MOD_CDS_TEXT,
tof_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,
tof_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 tof_types
INNER JOIN tof_models ON MOD_ID = TYP_MOD_ID
INNER JOIN tof_manufacturers ON MFA_ID = MOD_MFA_ID
INNER JOIN tof_country_designations AS COUNTRY_DESIGNATIONS2 ON COUNTRY_DESIGNATIONS2.CDS_ID = MOD_CDS_ID AND COUNTRY_DESIGNATIONS2.CDS_LNG_ID = @LNG_ID
INNER JOIN tof_des_texts AS DES_TEXTS7 ON DES_TEXTS7.TEX_ID = COUNTRY_DESIGNATIONS2.CDS_TEX_ID
INNER JOIN tof_country_designations ON tof_country_designations.CDS_ID = TYP_CDS_ID AND tof_country_designations.CDS_LNG_ID = @LNG_ID
INNER JOIN tof_des_texts ON tof_des_texts.TEX_ID = tof_country_designations.CDS_TEX_ID
LEFT JOIN tof_designations ON tof_designations.DES_ID = TYP_KV_ENGINE_DES_ID AND tof_designations.DES_LNG_ID = @LNG_ID
LEFT JOIN tof_des_texts AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = tof_designations.DES_TEX_ID
LEFT JOIN tof_designations AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = TYP_KV_FUEL_DES_ID AND DESIGNATIONS2.DES_LNG_ID = @LNG_ID
LEFT JOIN tof_des_texts AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS2.DES_TEX_ID
LEFT JOIN tof_link_typ_eng ON LTE_TYP_ID = TYP_ID
LEFT JOIN tof_engines ON ENG_ID = LTE_ENG_ID
LEFT JOIN tof_designations AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID = TYP_KV_BODY_DES_ID AND DESIGNATIONS3.DES_LNG_ID = @LNG_ID
LEFT JOIN tof_des_texts AS DES_TEXTS4 ON DES_TEXTS4.TEX_ID = DESIGNATIONS3.DES_TEX_ID
LEFT JOIN tof_designations AS DESIGNATIONS4 ON DESIGNATIONS4.DES_ID = TYP_KV_MODEL_DES_ID AND DESIGNATIONS4.DES_LNG_ID = @LNG_ID
LEFT JOIN tof_des_texts AS DES_TEXTS5 ON DES_TEXTS5.TEX_ID = DESIGNATIONS4.DES_TEX_ID
LEFT JOIN tof_designations AS DESIGNATIONS5 ON DESIGNATIONS5.DES_ID = TYP_KV_AXLE_DES_ID AND DESIGNATIONS5.DES_LNG_ID = @LNG_ID
LEFT JOIN tof_des_texts AS DES_TEXTS6 ON DES_TEXTS6.TEX_ID = DESIGNATIONS5.DES_TEX_ID
INNER JOIN tb_treetype AS TT ON TT.TCDTYPE = TYP_ID
INNER JOIN tb_treerange AS RT ON RT.RANGEID = TT.RANGEID AND RT.CLASSID = 1
WHERE TYP_MOD_ID = @MOD_ID
ORDER BY MFA_BRAND, MOD_CDS_TEXT, TYP_CDS_TEXT, TYP_PCON_START, TYP_CCM;