-- -- Brief description of tables -- ART_COUNTRY_SPECIFICS - Parts statuses: Normal, Not available, product shot with the production, etc. ART_LOOKUP - Search analogues parts ARTICLE_CRITERIA - Characteristics (criteria) parts : the installation location, size, etc. ARTICLE_INFO - Additional information about replacement parts ARTICLES - Main table for parts ARTICLE_LISTS - Table of specifications for parts that comes as a set. BRANDS - All manufacturers of spare parts (including original) COUNTRIES - countries COUNTRY_DESIGNATIONS - Links of other tables with text data (DES_TEXTS) CRITERIA - Characteristics (criteria) for general data (vehicles and parts): installation location, size, etc. DES_TEXTS - All text data DESIGNATIONS - Links of other tables with text data (DES_TEXTS) ENGINES - Car engines GRAFORARTICLES - table of images GENERIC_ARTICLES - parts groups LINK_ART - Links of parts with parts group LAID (used in determining the applicability of parts for cars) LA_CRITERIA - Criteria for the applicability of spare parts for vehicles LA_INFO - Additional information for the group of applicability of parts to the car LANGUAGES - languages texts LINK_ART_GA - Links of parts with parts group GA_ID LINK_GA_STR - Links of parts groups GA_ID with categories of parts STR_ID (used in the construction of the category tree parts) LINK_LA_TYP - Links of parts groups LAID with cars (used in determining the applicability of parts for cars) LINK_TYP_ENG - Bind vehicles and engines MANUFACTURERS - Car Manufacturers MODELS - Car model NUMBERPLATES_NL - Additional number plates of vehicles (used for the Netherlands) PRICES - table praces from suppliers SEARCH_TREE - Tree categories of parts SUPERSEDED_ARTICLES - Numbers of modified parts and their new counterparts SUPPLIER_ADDRESSES - Contact Information non-original manufacturers parts (Aftermarket) SUPPLIERS - Aftermarket parts manufacturers (suppliers TecDoc) TEXT_MODULE_TEXTS - Texts additional information on spare parts and eligibility criteria TEXT_MODULES - Additional information about replacement parts TYPE_NUMBERS - Additional numbers of cars (only used in the search for cars by KBA-number for Germany, and Mime-number for France) TYPES - Car types -- -- ================================================================================================================================== -- -- DESCRIPTION MAIN VALUES FOR TABLES LNG_ID - Language identification number ART_ID - Parts identification number TYP_ID - Cars identification number GA_ID - Commodity group identification number LA_ID - Group identification number of applicability of parts to the car STR_ID - Product categories identification number -- ART_COUNTRY_SPECIFICS - Parts statuses: Normal, Not available, product shot with the production, etc. -- ART_COUNTRY_SPECIFICS ACS_ART_ID - (article which attached information) key referencing table ARTICLES=>ART_ID ACS_PACK_UNIT - packaging unit ACS_KV_STATUS_DES_ID - (description of the status of the product, such as Normal or ) key referencing table DESIGNATIONS=>DES_ID ACS_KV_STATUS - Status numerically 0 - to prepare 1 - normal 2 - not available 5 - only supplied specifications 7 - pseudo-product 8 - Discontinued product 9 - no longer available ACS_STATUS_DATE - The start date when it acquired that status article (since when is not available, at what point is replaced, etc.) -- ---------------------------------------------------------- -- ART_LOOKUP - Search analogues parts -- ART_LOOKUP ARL_ART_ID - (detail) key referencing table ARTICLES=>ART_ID ARL_SEARCH_NUMBER - search number, without dots and spaces. ARL_KIND - type number: 1 - after market (the article) number, which includes analogs 2 - trade number (user number) 3 - original (structural) number 4 - after market analogue 5 - bar-code (number EAN) ARL_BRA_ID - (manufacturer) key referencing table BRANDS=>BRA_ID ARL_DISPLAY_NR - number, with dots and spaces, etc ARL_SORT - sorting -- ---------------------------------------------------------- -- ARTICLE_CRITERIA - Characteristics (criteria) parts : the installation location, size, etc. -- ARTICLE_CRITERIA ACR_ART_ID - (item for which the selected criteria) key referencing table ARTICLES=>ART_ID ACR_GA_ID - (groups of articles) key referencing table GENERIC_ARTICLES=>GA_ID ACR_SORT - field to sort the data in the format TecDoc ACR_CRI_ID - (criteries, name of criterie) key referencing table CRITERIA=>CRI_ID ACR_VALUE - numerical criterion value (if available) ACR_KV_DES_ID - string value of the criterion (if available) -- ---------------------------------------------------------- -- ARTICLE_INFO - Additional information about parts -- ARTICLE_INFO AIN_ART_ID - (item for which the selected info) key referencing table ARTICLES=>ART_ID AIN_GA_ID - (groups of articles) key referencing table GENERIC_ARTICLES=>GA_ID AIN_SORT - field to sort the data in the format TecDoc AIN_TMO_ID - (text) key referencing table TEXT_MODULES=>TMO_ID -- ---------------------------------------------------------- -- ARTICLES - Main table for parts -- ARTICLES ART_ID - key ART_ARTICLE_NR - number article. Text item number. Used in the price lists, stories, commentaries. ART_SUP_ID - (manufacturer - id brand) key referencing table SUPPLIERS=>SUP_ID ART_DES_ID - (product description) key referencing table DESIGNATIONS=>DES_ID ART_COMPLETE_DES_ID - (name of part) key referencing table DESIGNATIONS=>DES_ID ART_PACK_SELFSERVICE - for independent use or not (1/0) ART_MATERIAL_MARK - requires mandatory designations or not (1/0) ART_REPLACEMENT - replacement part or not (1/0) ART_ACCESSORY - accessory or not (1/0) -- ---------------------------------------------------------- -- ARTICLE_LISTS - Table of specifications for parts that comes as a set. -- ARTICLE_LISTS ALI_ART_ID - (item for those specifying) key referencing table ARTICLES=>ART_ID ALI_SORT - field to sort the data in the format TecDoc ALI_ART_ID_COMPONENT - (element specification) key referencing table ARTICLES=>ART_ID ALI_QUANTITY - number for the item specification ALI_GA_ID - (groups of articles) key referencing table GENERIC_ARTICLES=>GA_ID -- ---------------------------------------------------------- -- BRANDS - All manufacturers of spare parts (including original) -- BRANDS BRA_ID - key BRA_MFC_CODE - name manufacturer (short) BRA_BRAND - name manufacturer (full) BRA_MF_NR - number brand according TecDoc -- ---------------------------------------------------------- -- COUNTRIES - countries -- COUNTRIES COU_ID - key COU_CC - code of country (3-symbol) COU_DES_ID - (name country) key referencing table DESIGNATIONS=>DES_ID COU_CURRENCY_CODE - currency code COU_ISO2 - code of country (2-symbol), corresponds to Internet domain names of the countries: de, ru and etc COU_IS_GROUP - 0 - specific country, 1 - groups country P.S. in our database, all the data have been exported for Europe where COU_ID = 248. So that the this table can skip -- ---------------------------------------------------------- -- COUNTRY_DESIGNATIONS - Links of other tables with text data (DES_TEXTS) COUNTRY_DESIGNATIONS CDS_ID - key CDS_LNG_ID - (language text) key referencing table LANGUAGES=>LNG_ID CDS_TEX_ID - (text) key referencing table DES_TEXTS=>TEX_ID -- ---------------------------------------------------------- -- CRITERIA - Characteristics (criteria) for general data (vehicles and parts): installation location, size, etc. -- CRITERIA CRI_ID - key CRI_DES_ID - (description of the criteria, does not appear in TecDoc) key referencing table DESIGNATIONS=>DES_ID CRI_SHORT_DES_ID - (a brief description of the criterion) key referencing table DESIGNATIONS=>DES_ID CRI_UNIT_DES_ID - (unit measurement of criteria) key referencing table DESIGNATIONS=>DES_ID CRI_TYPE - type of criteria A - (almost the same N) criterion of text, but it is contained in VALUE (ARTICLE_CRITERIA=>ACR_VALUE) B - information number D - years of release (almost the same N) criterion text, but it is contained in VALUE (ARTICLE_CRITERIA=>ACR_VALUE) K - criterion text value contained in DES_ID (ARTICLE_CRITERIA=>ACR_KV_DES_ID), for these criteria need to consider the value of KV_KT_ID N - criterion of number, meaning contained in VALUE (ARTICLE_CRITERIA=>ACR_VALUE) V - criterion is itself a value. Filels DES_ID or VALUE empty. CRI_KT_ID - (a list of possible settings for which they want this list here - unknown) key referencing table KEY_VALUES=>KV_KT_ID CRI_IS_INTERVAL - This criterion is whether or not the range of values (1/0) CRI_SUCCESSOR - (The second criterion. Only makes sense if CRI_IS_INTERVAL=1) key referencing table CRITERIA=>CRI_ID -- ---------------------------------------------------------- -- DES_TEXTS - All text data -- DES_TEXTS TEX_ID - key TEX_TEXT - field MEMO, text information -- ---------------------------------------------------------- -- DESIGNATIONS - Links of other tables with text data (DES_TEXTS) DESIGNATIONS DES_ID - key DES_LNG_ID - (language text) key referencing table LANGUAGES=>LNG_ID DES_TEX_ID - (text) key referencing table DES_TEXTS=>TEX_ID -- ---------------------------------------------------------- -- ENGINES - Car engines -- ENGINES ENG_ID - key ENG_MFA_ID - manufacturers cars ENG_CODE - code engine ENG_PCON_START - start year release ENG_PCON_END - end year release ENG_KW_FROM - KW from ENG_KW_UPTO - KW to ENG_HP_FROM - HP from ENG_HP_UPTO - HO to ENG_VALVES - number valves ENG_CYLINDERS - number cylinders ENG_CCM_FROM - CCM from END_CCM_UPTO - CCM to ENG_KV_DESIGN_DES_ID - ENG_KV_FUEL_TYPE_DES_ID - ENG_KV_FUEL_SUPPLY_DES_ID - ENG_DESCRIPTION - ENG_KV_ENGINE_DES_ID - ENG_KW_RPM_FROM - ENG_KW_RPM_UPTO - ENG_TORQUE_FROM - ENG_TORQUE_UPTO - ENG_TORQUE_RPM_FROM - ENG_TORQUE_RPM_UPTO - ENG_COMPRESSION_FROM - ENG_COMPRESSION_UPTO - ENG_DRILLING - ENG_EXTENSION - ENG_CRANKSHAFT - ENG_KV_CHARGE_DES_ID - ENG_KV_GAS_NORM_DES_ID - ENG_KV_CYLINDERS_DES_ID - ENG_KV_CONTROL_DES_ID - ENG_KV_VALVE_CONTROL_DES_ID - ENG_KV_COOLING_DES_ID - ENG_KV_CCM_TAX_FROM - ENG_KV_CCM_TAX_UPTO - ENG_LITRES_TAX_FROM - ENG_LITRES_TAX_UPTO - ENG_LITRES_FROM - ENG_LITRES_UPTO - ENG_KV_USE_DES_ID - p.s. many of these parameters are described in table TYPES -- ---------------------------------------------------------- -- Table structure for table `GRAFORARTICLES` -- table of images -- GRAFORARTICLES GRA_HOST -- name of hosting, for example : http://site.com GRA_MAINFOLDER -- main folder where placed folders with images, for example : partsimages GRA_SUPID -- Name of folder. ID number of brand. GRA_ARTID -- Name of parts. ID number of parts. GRA_PREFICS -- prefix file name, this value will only then when for one part will several images GRA_EXT -- graphic format of images GRA_KIND type : 1-images, 3-logos -- ---------------------------------------------------------- -- GENERIC_ARTICLES - parts groups -- GENERIC_ARTICLES GA_ID - key GA_NR - number for each group of articles for TecDoc GA_DES_ID - (the full name of the group to which belongs article) key referencing table DESIGNATIONS=>DES_ID GA_DES_ID_STANDARD - (Part group name) key referencing table DESIGNATIONS=>DES_ID GA_DES_ID_ASSEMBLY - (place where the set is attached) key referencing table DESIGNATIONS=>DES_ID GA_DES_ID_INTENDED - (in is included) key referencing table DESIGNATIONS=>DES_ID -- ----------------------------------------------------------- -- LINK_ART - Links of parts with parts group LAID (used in determining the applicability of parts for cars) -- LINK_ART LA_ID - key LA_ART_ID - (article) key referencing table ARTICLES=>ART_ID LA_GA_ID - (groups of article) key referencing table GENERIC_ARTICLES=>GA_ID LA_SORT - field to sort the data in the format TecDoc -- ------------------------------------------------------------ -- LA_CRITERIA - Criteria for the applicability of spare parts for vehicles -- LA_CRITERIA LAC_LA_ID - key for groups details of applicability to the car LINK_ART=>LA_ID LAC_SORT - field to sort the data in the format TecDoc (по порядку критериев) LAC_CRI_ID - key referencing table CRITERIA=>CRI_ID LAC_VALUE - value of the criterion LAC_KV_DES_ID - dimension of criterion -- ------------------------------------------------------------ -- LA_INFO - Additional information for the group of applicability of parts to the car --LA_INFO LIN_LA_ID - key for groups details of applicability to the car LINK_ART=>LA_ID LIN_SORT - field to sort the data in the format TecDoc LIN_TMO_ID - (text) key referencing table TEXT_MODULES=>TMO_ID -- ------------------------------------------------------------ -- LANGUAGES - languages texts -- LANGUAGES LNG_ID - key LNG_DES_ID - (name language) key referencing table DESIGNATIONS=>DES_ID LNG_ISO2 - iso2 LNG_CODEPAGE - cope page -- ------------------------------------------------------------ -- LINK_ART_GA - Links of parts with parts group GA_ID -- LINK_ART_GA LAG_ART_ID - (article) key referencing table ARTICLES=>ART_ID LAG_GA_ID - (groups of articles) key referencing table GENERIC_ARTICLES=>GA_ID LAG_SUP_ID - (manufacturer) key referencing table SUPPLIERS=>SUP_ID -- ------------------------------------------------------------ -- LINK_GA_STR - Links of parts groups GA_ID with categories of parts STR_ID (used in the construction of the category tree parts) -- LINK_GA_STR LGS_STR_ID - (Product Categories) key referencing table SEARCH_TREE=>STR_ID LGS_GA_ID - (groups of articles) key referencing table GENERIC_ARTICLES=>GA_ID -- ------------------------------------------------------------ -- LINK_LA_TYP - Links of parts groups LAID with cars (used in determining the applicability of parts for cars) -- LINK_LA_TYP LAT_TYP_ID - key referencing table TYPES=>TYP_ID LAT_LA_ID - key referencing table LINK_ART=>LA_ID LAT_GA_ID - (groups of articles) key referencing table GENERIC_ARTICLES=>GA_ID LAT_SUP_ID - (manufacturer) key referencing table SUPPLIERS=>SUP_ID LAT_SORT - field to sort the data in the format TecDoc -- ------------------------------------------------------------ -- LINK_TYP_ENG - Bind vehicles and engines -- LINK_TYP_ENG LTE_TYP_ID - key referencing table TYPES=>TYP_ID LTE_ENG_ID - key referencing table ENGINES=>ENG_ID LTE_PCON_START - year begin release engine LTE_PCON_END - year end release engine -- ------------------------------------------------------------- -- MANUFACTURERS - Car Manufacturers -- MANUFACTURERS MFA_ID - key MFA_PC_MFC - presence / absence of the manufacturer in the list of passanger car manufacturers (1 - availability, 0 - no) MFA_CV_MFC - presence / absence of the manufacturer in the list of truck car manufacturers MFA_ENG_MFC - presence / absence of the manufacturer in the list of engine manufacturers MFA_AXL_MFC - presence / absence of the manufacturer in the list of axle manufacturers MFA_MFC_CODE - abbreviated name of the manufacturer MFA_BRAND - full name of the manufacturer (BRANDS=>BRA_BRAND). MFA_MF_NR - number manufacturer for TECDOC. (BRANDS=>BRA_MF_NR) -- ------------------------------------------------------------- -- MODELS - Car model -- MODELS MOD_ID - key MOD_MFA_ID - (manufacturer) key referencing table MANUFACTURERS=>MFA_ID MOD_CDS_ID - (name model) key referencing table COUNTRY_DESIGNATIONS=>CDS_ID MOD_PCON_START - first 4 digits: year of the begin production model of the sequence, the last two it month of the production model. MOD_PCON_END - first 4 digits: year of the end production model of the sequence, the last two it month of the production model. MOD_PC - presence / absence of the manufacturer in the list of passanger car manufacturers (1 - availability, 0 - no) MOD_CV - presence / absence of the manufacturer in the list of truck car manufacturers (1 - availability, 0 - no) -- ------------------------------------------------------------- -- NUMBERPLATES_NL - Additional number plates of vehicles (used for the Netherlands) -- NUMBERPLATES_NL NNL_NUMBERPLATE - number plate NNL_TYP_ID - key referencing table TYPES=>TYP_ID -- ------------------------------------------------------------- -- SEARCH_TREE - Tree categories of parts -- SEARCH_TREE STR_ID - key STR_ID_PARENT - (link to the parent list item) =>STR_ID STR_TYPE - type of product group 1 - passanger cars 2 - truck cars 3 - engine 4 - universal 5 - axle STR_LEVEL - nesting level (1 - the highest level) STR_DES_ID - the name of the list item (product group) STR_SORT - sorting STR_NODE_NR - node number -- ------------------------------------------------------------- -- SUPERSEDED_ARTICLES - Numbers of modified parts and their new counterparts -- SUPERSEDED_ARTICLES SUA_ART_ID - (article) key referencing table ARTICLES=>ART_ID SUA_NUMBER - new part Number -- ------------------------------------------------------------- -- SUPPLIER_ADDRESSES - Contact Information non-original manufacturers parts (Aftermarket) -- SUPPLIER_ADDRESSES SAD_SUP_ID - key SAD_TYPE_OF_ADDRESS - type of address SAD_COU_ID - (country manufacturer) key referencing table COUNTRIES=>COU_ID SAD_NAME1 - Extended manufacturers name SAD_NAME2 - Additional manufacturers name SAD_STREET1 - address1 SAD_STREET2 - address2 SAD_POB - postbox SAD_COU_ID_POSTAL - postal country, key referencing table COUNTRIES=>COU_ID SAD_POSTAL_CODE_PLACE - Index (city) SAD_POSTAL_CODE_POB - Index (postbox) SAD_POSTAL_CODE_CUST - SAD_CITY1 - city1 SAD_CITY2 - city2 SAD_TEL - contact phone SAD_FAX - fax SAD_EMAIL - Email SAD_WEB - WEB site -- ------------------------------------------------------------- -- SUPPLIERS - Aftermarket parts manufacturers (suppliers TecDoc) -- SUPPLIERS SUP_ID - key SUP_BRAND - name brand SUP_SUPPLIER_NR - number manufacturer for TECDOC. -- ------------------------------------------------------------- -- TEXT_MODULE_TEXTS - Texts additional information on spare parts and eligibility criteria -- TEXT_MODULE_TEXTS TMT_ID - key TMT_TEXT - text (binary) -- ------------------------------------------------------------- -- TEXT_MODULES - Additional information about replacement parts -- TEXT_MODULES TMO_ID - key TMO_LNG_ID - (language text) key referencing table LANGUAGES=>LNG_ID TMO_TMT_ID - (text) key referencing table TEXT_MODULE_TEXTS=>TMT_ID -- ------------------------------------------------------------- -- TYPE_NUMBERS - Additional numbers of cars (only used in the search for cars by KBA-number for Germany, and Mime-number for France) -- TYPE_NUMBERS TYN_TYP_ID - key referencing table TYPES=>TYP_ID TYN_SEARCH_TEXT - number or code search TYN_KIND - type number : 1-for Germany, search by KBA; 2-for France, search by MIME -- ------------------------------------------------------------- -- TYPES - Car types -- TYPES TYP_ID - key TYP_CDS_ID - (short car type name) key referencing table COUNTRY_DESIGNATIONS=>CDS_ID TYP_MMT_CDS_ID - (full car type name) key referencing table COUNTRY_DESIGNATIONS=>CDS_ID TYP_MOD_ID - (model car) key referencing table MODELS=>MOD_ID TYP_SORT - number in the list for a particular model, selected from a list . TYP_PCON_START - first 4 digits: the begin production type car year, the last two month of production type car. TYP_PCON_END - first 4 digits: the end production type car year, the last two month of production type car. TYP_KW_FROM - Technical information / Motor Power (kW) (from) TYP_KW_UPTO - Technical information / Motor Power (kW) (to) TYP_HP_FROM - Technical information / Engine power (hp) (from) TYP_HP_UPTO - Technical information / Engine power (hp) (to) TYP_CCM - Technical Support / Information Tech. The volume of CCM TYP_CYLINDERS - Technical information / cylinder (number cylinders) TYP_DOORS - Design / number doors. TYP_TANK - Design / tank TYP_KV_VOLTAGE_DES_ ID - (voltage) key, referencing table DESIGNATIONS=>DES_ID TYP_KV_ABS_DES_ID - (Technical information/ABS) key, referencing table DESIGNATIONS=>DES_ID TYP_KV_ASR_DES_ID - (Technical information/ASR) key, referencing table DESIGNATIONS=>DES_ID TYP_KV_ENGINE_DES_ID - (Technical information / Engine type) key, referencing table DESIGNATIONS=>DES_ID TYP_KV_BRAKE_TYPE_DES_ID - (Technical information / brake type) key, referencing table DESIGNATIONS=>DES_ID TYP_KV_BRAKE_SYST_DES_ID - (Technical information / brake system) key, referencing table DESIGNATIONS=>DES_ID TYP_KV_FUEL_DES_ ID - (Technical information / Fuel type) key, referencing table DESIGNATIONS=>DES_ID TYP_KV_CATALYST_DES_ID - (Technical information / catalyst type) key, referencing table DESIGNATIONS=>DES_ID TYP_KV_BODY_DES_ID - (Technical information / Type of construction) key, referencing table DESIGNATIONS=>DES_ID (relevant only for passenger cars) TYP_MAX_WEIGHT - Design / Tonnage TYP_KV_MODEL_DES_ID - (Technical information / Type of construction) key, referencing table DESIGNATIONS=>DES_ID (relevant only for truck cars) TYP_KV_AXLE_DES_ID - (Design / Configuration axis) key, referencing table DESIGNATIONS=>DES_ID TYP_CCM_TAX - Technical information / Technical CCM TYP_LITRES - Technical information / Engine liters TYP_KV_DRIVE_DES_ID - (Design/drive type) key, referencing table DESIGNATIONS=>DES_ID TYP_KV_TRANS_DES_ID - (Technical information / transmision type) key, referencing table DESIGNATIONS=>DES_ID TYP_KV_FUEL_SUPPLY_DES_ID - (Technical information / refueling) key, referencing table DESIGNATIONS=>DES_ID TYP_VALVES - Technical information / valves per combustion chamber -- ------------------------------------------------------------- -- -- ============================================================================================== -- -- MAIN SQL-query 0. Start 0.1. Selecting the desired language SET @LNGID=4; -- by default English language (for first start) SELECT LANGUAGES.LNG_ID, DES_TEXTS.TEX_TEXT AS NAMELANGUAGE, LANGUAGES.LNG_ISO2, LANGUAGES.LNG_CODEPAGE FROM LANGUAGES INNER JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = LANGUAGES.LNG_DES_ID INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID WHERE DESIGNATIONS.DES_LNG_ID=@LNGID ORDER BY NAMELANGUAGE; -- select the desired language (LNG_ID) 1. For use with car 1.1. Choosing a brand of car (with the possibility of separation of truck and passenger) SELECT MFA_ID, MFA_BRAND FROM MANUFACTURERS WHERE (MFA_PC_MFC=1) -- only passanger cars OR (MFA_CV_MFC=1) -- only truck cars ORDER BY MFA_BRAND; -- select the desired brand of car (MFA_ID) 1.2. Choosing a model of car (with the possibility of separation of truck and passenger) SET @MFAID=511; -- for example its BMW with MFA_ID=511 SET @LNGID=4; -- by default English language SELECT DISTINCT MODELS.MOD_ID, MANUFACTURERS.MFA_BRAND AS BRANDCAR, DES_TEXTS.TEX_TEXT AS MODELCAR, MODELS.MOD_PCON_START, -- YYYYMM: Year / month of the begin production model */ MODELS.MOD_PCON_END -- YYYYMM: Year / month of the end production model (NULL - not limited) */ FROM MODELS INNER JOIN MANUFACTURERS ON (MODELS.MOD_MFA_ID=MANUFACTURERS.MFA_ID) INNER JOIN COUNTRY_DESIGNATIONS ON COUNTRY_DESIGNATIONS.CDS_ID = MODELS.MOD_CDS_ID INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = COUNTRY_DESIGNATIONS.CDS_TEX_ID WHERE (MANUFACTURERS.MFA_ID=@MFAID) AND (COUNTRY_DESIGNATIONS.CDS_LNG_ID=@LNGID) AND ((MODELS.MOD_PC=1) -- only passanger cars OR (MODELS.MOD_CV=1)) -- only truck cars ORDER BY BRANDCAR, MODELCAR; -- select the desired model of car (MOD_ID) 1.3. Choosing a type of car SET @MODID=3397; -- for example its BMW E(46) SET @LNGID=4; -- by default English language SELECT DISTINCT TYP_ID, MANUFACTURERS.MFA_BRAND AS BRANDCAR, DES_TEXTS3.TEX_TEXT AS MODELCAR, DES_TEXTS1.TEX_TEXT AS SHORTNAMETYPECAR, DES_TEXTS2.TEX_TEXT AS FULLNAMETYPECAR, TYPES.TYP_PCON_START, -- YYYYMM: Year / month of the begin production type */ TYPES.TYP_PCON_END -- YYYYMM: Year / month of the begin production type (NULL - not limited) */ FROM TYPES INNER JOIN MODELS ON (TYPES.TYP_MOD_ID=MODELS.MOD_ID) INNER JOIN MANUFACTURERS ON (MANUFACTURERS.MFA_ID=MODELS.MOD_MFA_ID) LEFT JOIN COUNTRY_DESIGNATIONS AS COUNTRY_DESIGNATIONS1 ON COUNTRY_DESIGNATIONS1.CDS_ID = TYPES.TYP_CDS_ID LEFT JOIN DES_TEXTS AS DES_TEXTS1 ON DES_TEXTS1.TEX_ID = COUNTRY_DESIGNATIONS1.CDS_TEX_ID LEFT JOIN COUNTRY_DESIGNATIONS AS COUNTRY_DESIGNATIONS2 ON COUNTRY_DESIGNATIONS2.CDS_ID = TYPES.TYP_MMT_CDS_ID LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = COUNTRY_DESIGNATIONS2.CDS_TEX_ID LEFT JOIN COUNTRY_DESIGNATIONS AS COUNTRY_DESIGNATIONS3 ON COUNTRY_DESIGNATIONS3.CDS_ID = MODELS.MOD_CDS_ID LEFT JOIN DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = COUNTRY_DESIGNATIONS3.CDS_TEX_ID WHERE (MODELS.MOD_ID=@MODID) AND (COUNTRY_DESIGNATIONS1.CDS_LNG_ID=@LNGID) AND (COUNTRY_DESIGNATIONS2.CDS_LNG_ID=@LNGID) AND (COUNTRY_DESIGNATIONS3.CDS_LNG_ID=@LNGID) -- ------------------------- AND ((MODELS.MOD_PC=1) -- only passanger cars OR (MODELS.MOD_CV=1)) -- only truck cars ORDER BY BRANDCAR, MODELCAR, FULLNAMETYPECAR; -- select the desired type of car (TYP_ID) 1.4. Criteries of cars SET @TYPID=9045; -- for example its BMW 3 (E46) 320 d SET @LNGID=4; -- by default English language SELECT TYPES.TYP_ID, (SELECT GROUP_CONCAT(DES_TEXTS1.TEX_TEXT) FROM DES_TEXTS AS DES_TEXTS1 JOIN COUNTRY_DESIGNATIONS AS COUNTRY_DESIGNATIONS1 ON (DES_TEXTS1.TEX_ID = COUNTRY_DESIGNATIONS1.CDS_TEX_ID) WHERE (COUNTRY_DESIGNATIONS1.CDS_ID = TYPES.TYP_MMT_CDS_ID) AND (COUNTRY_DESIGNATIONS1.CDS_LNG_ID=@LNGID) ) AS FULLNAMETYPECAR, TYPES.TYP_PCON_START, TYPES.TYP_PCON_END, CASE WHEN (SELECT DES_TEXTS2.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS2 JOIN DESIGNATIONS AS DESIGNATIONS2 ON (DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID) WHERE (DESIGNATIONS2.DES_ID = TYPES.TYP_KV_BODY_DES_ID) AND (DESIGNATIONS2.DES_LNG_ID=@LNGID)) IS NULL THEN (SELECT DES_TEXTS3.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS3 JOIN DESIGNATIONS AS DESIGNATIONS3 ON (DES_TEXTS3.TEX_ID = DESIGNATIONS3.DES_TEX_ID) WHERE (DESIGNATIONS3.DES_ID = TYPES.TYP_KV_MODEL_DES_ID) AND (DESIGNATIONS3.DES_LNG_ID=@LNGID)) ELSE (SELECT DES_TEXTS4.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS4 JOIN DESIGNATIONS AS DESIGNATIONS4 ON (DES_TEXTS4.TEX_ID = DESIGNATIONS4.DES_TEX_ID) WHERE (DESIGNATIONS4.DES_ID = TYPES.TYP_KV_BODY_DES_ID) AND (DESIGNATIONS4.DES_LNG_ID=@LNGID)) END AS BODYTYPE, TYPES.TYP_KW_FROM, TYPES.TYP_KW_UPTO, -- You can skip the data have not for all cars TYPES.TYP_HP_FROM, TYPES.TYP_HP_UPTO, -- You can skip the data have not for all cars TYPES.TYP_CCM, TYPES.TYP_CYLINDERS, TYPES.TYP_DOORS, -- You can skip the data have not for all cars TYPES.TYP_TANK, -- You can skip the data have not for all cars (SELECT DES_TEXTS5.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS5 JOIN DESIGNATIONS AS DESIGNATIONS5 ON (DES_TEXTS5.TEX_ID = DESIGNATIONS5.DES_TEX_ID) WHERE (DESIGNATIONS5.DES_ID = TYPES.TYP_KV_VOLTAGE_DES_ID) AND (DESIGNATIONS5.DES_LNG_ID=@LNGID) ) AS VOLTAGE, -- You can skip the data have not for all cars (SELECT DES_TEXTS6.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS6 JOIN DESIGNATIONS AS DESIGNATIONS6 ON (DES_TEXTS6.TEX_ID = DESIGNATIONS6.DES_TEX_ID) WHERE (DESIGNATIONS6.DES_ID = TYPES.TYP_KV_ABS_DES_ID) AND (DESIGNATIONS6.DES_LNG_ID=@LNGID) ) AS TYPEABS, (SELECT DES_TEXTS7.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS7 JOIN DESIGNATIONS AS DESIGNATIONS7 ON (DES_TEXTS7.TEX_ID = DESIGNATIONS7.DES_TEX_ID) WHERE (DESIGNATIONS7.DES_ID = TYPES.TYP_KV_ASR_DES_ID) AND (DESIGNATIONS7.DES_LNG_ID=@LNGID) ) AS TYPEASR, (SELECT DES_TEXTS8.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS8 JOIN DESIGNATIONS AS DESIGNATIONS8 ON (DES_TEXTS8.TEX_ID = DESIGNATIONS8.DES_TEX_ID) WHERE (DESIGNATIONS8.DES_ID = TYPES.TYP_KV_ENGINE_DES_ID) AND (DESIGNATIONS8.DES_LNG_ID=@LNGID) ) AS TYPEENGINE, (SELECT GROUP_CONCAT(ENGINES.ENG_CODE) FROM ENGINES JOIN LINK_TYP_ENG ON (ENGINES.ENG_ID=LINK_TYP_ENG.LTE_ENG_ID) WHERE LINK_TYP_ENG.LTE_TYP_ID=TYPES.TYP_ID) as LISTCODEENGINE, (SELECT DES_TEXTS9.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS9 JOIN DESIGNATIONS AS DESIGNATIONS9 ON (DES_TEXTS9.TEX_ID = DESIGNATIONS9.DES_TEX_ID) WHERE (DESIGNATIONS9.DES_ID = TYPES.TYP_KV_BRAKE_TYPE_DES_ID) AND (DESIGNATIONS9.DES_LNG_ID=@LNGID) ) AS BRAKETYPE, (SELECT DES_TEXTS10.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS10 JOIN DESIGNATIONS AS DESIGNATIONS10 ON (DES_TEXTS10.TEX_ID = DESIGNATIONS10.DES_TEX_ID) WHERE (DESIGNATIONS10.DES_ID = TYPES.TYP_KV_BRAKE_SYST_DES_ID) AND (DESIGNATIONS10.DES_LNG_ID=@LNGID) ) AS BRAKESYSTEM, (SELECT DES_TEXTS11.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS11 JOIN DESIGNATIONS AS DESIGNATIONS11 ON (DES_TEXTS11.TEX_ID = DESIGNATIONS11.DES_TEX_ID) WHERE (DESIGNATIONS11.DES_ID = TYPES.TYP_KV_FUEL_DES_ID) AND (DESIGNATIONS11.DES_LNG_ID=@LNGID) ) AS TYPEFUEL, (SELECT DES_TEXTS12.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS12 JOIN DESIGNATIONS AS DESIGNATIONS12 ON (DES_TEXTS12.TEX_ID = DESIGNATIONS12.DES_TEX_ID) WHERE (DESIGNATIONS12.DES_ID = TYPES.TYP_KV_CATALYST_DES_ID) AND (DESIGNATIONS12.DES_LNG_ID=@LNGID) ) AS TYPECATALYST, TYPES.TYP_MAX_WEIGHT, -- You can skip the data have not for all cars (SELECT DES_TEXTS13.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS13 JOIN DESIGNATIONS AS DESIGNATIONS13 ON (DES_TEXTS13.TEX_ID = DESIGNATIONS13.DES_TEX_ID) WHERE (DESIGNATIONS13.DES_ID = TYPES.TYP_KV_AXLE_DES_ID) AND (DESIGNATIONS13.DES_LNG_ID=@LNGID) ) AS TYPEAXLE, -- You can skip the data have not for all cars TYPES.TYP_CCM_TAX, TYPES.TYP_LITRES, TYPES.TYP_VALVES, (SELECT DES_TEXTS14.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS14 JOIN DESIGNATIONS AS DESIGNATIONS14 ON (DES_TEXTS14.TEX_ID = DESIGNATIONS14.DES_TEX_ID) WHERE (DESIGNATIONS14.DES_ID = TYPES.TYP_KV_DRIVE_DES_ID) AND (DESIGNATIONS14.DES_LNG_ID=@LNGID) ) AS TYPEDRIVE, (SELECT DES_TEXTS15.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS15 JOIN DESIGNATIONS AS DESIGNATIONS15 ON (DES_TEXTS15.TEX_ID = DESIGNATIONS15.DES_TEX_ID) WHERE (DESIGNATIONS15.DES_ID = TYPES.TYP_KV_TRANS_DES_ID) AND (DESIGNATIONS15.DES_LNG_ID=@LNGID) ) AS TYPETRANS, (SELECT DES_TEXTS16.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS16 JOIN DESIGNATIONS AS DESIGNATIONS16 ON (DES_TEXTS16.TEX_ID = DESIGNATIONS16.DES_TEX_ID) WHERE (DESIGNATIONS16.DES_ID = TYPES.TYP_KV_FUEL_SUPPLY_DES_ID) AND (DESIGNATIONS16.DES_LNG_ID=@LNGID) ) AS FUELSUPLY FROM TYPES WHERE (TYPES.TYP_ID=@TYPID) -- ------------------------- -- separate list of codes used to auto engines SELECT ENGINES.ENG_ID, ENGINES.ENG_CODE FROM ENGINES JOIN LINK_TYP_ENG ON (ENGINES.ENG_ID=LINK_TYP_ENG.LTE_ENG_ID) WHERE LINK_TYP_ENG.LTE_TYP_ID=@TYPID 1.5. Search for car number plate, code KBA or MIME -- on a plate for the Netherlands SET @NUMBERPLATE='01DPPR'; SELECT NNL_TYP_ID, NNL_NUMBERPLATE FROM NUMBERPLATES_NL WHERE NNL_NUMBERPLATE=@NUMBERPLATE -- by number KBA for Germany SET @KBA_NR1 = '0005'; SET @KBA_NR2 = '624'; SET @KBA_NR = CONCAT(@KBA_NR1, @KBA_NR2); /* 0005624 */ SELECT TYN_TYP_ID, TYN_SEARCH_TEXT FROM TYPE_NUMBERS WHERE (TYN_SEARCH_TEXT=@KBA_NR) AND (TYN_KIND=1) -- by number MIME for France SET @MIME_NR = 'MBM5512CK851'; SELECT TYN_TYP_ID, TYN_SEARCH_TEXT FROM TYPE_NUMBERS WHERE (TYN_SEARCH_TEXT=@MIME_NR) AND (TYN_KIND=2) 2. To work with the parts 2.1. Data of parts SET @ARTID = 1050102; /* 24.5262-0575.3 [ATE] - Brake hose */ SET @LNGID=4; -- by default English language SELECT ART_ID, ART_ARTICLE_NR, SUP_BRAND, DES_TEXTS.TEX_TEXT AS ART_COMPLETE_DES_TEXT, DES_TEXTS2.TEX_TEXT AS ART_DES_TEXT, DES_TEXTS3.TEX_TEXT AS ART_STATUS_TEXT FROM ARTICLES INNER JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = ART_COMPLETE_DES_ID AND DESIGNATIONS.DES_LNG_ID = @LNGID INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = ART_DES_ID AND DESIGNATIONS2.DES_LNG_ID = @LNGID LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID INNER JOIN SUPPLIERS ON SUP_ID = ART_SUP_ID INNER JOIN ART_COUNTRY_SPECIFICS ON ACS_ART_ID = ART_ID INNER JOIN DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID = ACS_KV_STATUS_DES_ID AND DESIGNATIONS3.DES_LNG_ID = @LNGID INNER JOIN DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS3.DES_TEX_ID WHERE ART_ID = @ARTID 2.2. Criteries of parts SET @ARTID = 1050102; /* 24.5262-0575.3 [ATE] - Brake hose */ SET @LNGID=4; -- by default English language SELECT DES_TEXTS.TEX_TEXT AS CRITERIA_DES_TEXT, IFNULL(DES_TEXTS2.TEX_TEXT, ACR_VALUE) AS CRITERIA_VALUE_TEXT FROM ARTICLE_CRITERIA LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = ACR_KV_DES_ID LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID LEFT JOIN CRITERIA ON CRI_ID = ACR_CRI_ID LEFT JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = CRI_DES_ID LEFT JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID WHERE ACR_ART_ID = @ARTID AND (DESIGNATIONS.DES_LNG_ID IS NULL OR DESIGNATIONS.DES_LNG_ID = @LNGID) AND (DESIGNATIONS2.DES_LNG_ID IS NULL OR DESIGNATIONS2.DES_LNG_ID = @LNGID) 2.3. Addtional info of parts SET @ARTID = 1221556; /* 55181 [RUVILLE] - Ролик */ SET @LNGID=4; -- by default English language SELECT TMT_TEXT AS INFOTEXT FROM ARTICLE_INFO INNER JOIN TEXT_MODULES ON TMO_ID = AIN_TMO_ID INNER JOIN TEXT_MODULE_TEXTS ON TMT_ID = TMO_TMT_ID WHERE AIN_ART_ID = @ARTID AND TMO_LNG_ID = @LNGID ORDER BY INFOTEXT 2.4. Analogous for parts and EAN -- list of analogues for Part SET @ARTID = 1050102; /* 24.5262-0575.3 [ATE] - Brake hose */ SELECT ARL_KIND, CASE WHEN ART_LOOKUP.ARL_KIND = 2 THEN SUPPLIERS.SUP_BRAND WHEN ART_LOOKUP.ARL_KIND IN (3, 4) THEN BRANDS.BRA_BRAND WHEN ART_LOOKUP.ARL_KIND = 5 THEN 'EAN' ELSE '' END AS BRAND, ARL_DISPLAY_NR FROM ART_LOOKUP LEFT JOIN BRANDS ON BRA_ID = ARL_BRA_ID INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_ART_ID INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID WHERE ARL_ART_ID = @ARTID AND ARL_KIND IN (2, 3, 4, 5) ORDER BY BRAND, ARL_SORT 2.5. Applicability to cars SET @ARTID = 1050102; /* 24.5262-0575.3 [ATE] - Brake hose */ SET @LNGID=4; -- by default English language 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 LINK_ART INNER JOIN LINK_LA_TYP ON LAT_LA_ID = LA_ID INNER JOIN TYPES ON TYP_ID = LAT_TYP_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 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 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 LA_ART_ID = @ARTID AND COUNTRY_DESIGNATIONS.CDS_LNG_ID = @LNGID AND COUNTRY_DESIGNATIONS2.CDS_LNG_ID = @LNGID AND (DESIGNATIONS.DES_LNG_ID IS NULL OR DESIGNATIONS.DES_LNG_ID = @LNGID) AND (DESIGNATIONS2.DES_LNG_ID IS NULL OR DESIGNATIONS2.DES_LNG_ID = @LNGID) AND (DESIGNATIONS3.DES_LNG_ID IS NULL OR DESIGNATIONS3.DES_LNG_ID = @LNGID) AND (DESIGNATIONS4.DES_LNG_ID IS NULL OR DESIGNATIONS4.DES_LNG_ID = @LNGID) AND (DESIGNATIONS5.DES_LNG_ID IS NULL OR DESIGNATIONS5.DES_LNG_ID = @LNGID) ORDER BY MFA_BRAND, MOD_CDS_TEXT, TYP_CDS_TEXT, TYP_PCON_START, TYP_CCM 2.6. Search for spare parts on the code and name -- Search by name parts SET @TYPID=9045; -- for example its BMW 3 (E46) 320 d SET @LNGID=4; -- by default English language SET @NAMESEARCH = '%brake%disk%'; /* Gaps in the search text and replace it with% also add at the beginning and end of the text */ SELECT ART_ID, SUP_BRAND, ART_ARTICLE_NR, DES_TEXTS.TEX_TEXT AS STR_DES_TEXT, DES_TEXTS2.TEX_TEXT AS ART_COMPLETE_DES_TEXT FROM LINK_GA_STR INNER JOIN LINK_LA_TYP ON LAT_TYP_ID = @TYPID AND LAT_GA_ID = LGS_GA_ID INNER JOIN LINK_ART ON LA_ID = LAT_LA_ID INNER JOIN SEARCH_TREE ON STR_ID = LGS_STR_ID INNER JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = STR_DES_ID AND DESIGNATIONS.DES_LNG_ID = @LNGID INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID INNER JOIN ARTICLES ON ART_ID = LA_ART_ID INNER JOIN SUPPLIERS ON SUP_ID = ART_SUP_ID INNER JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = ART_COMPLETE_DES_ID INNER JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID AND DESIGNATIONS2.DES_LNG_ID = @LNGID WHERE DES_TEXTS.TEX_TEXT LIKE @NAMESEARCH -- search the code parts (initial choice brand for search numbers) SET @LNGID=4; -- by default English language SET @SEARCHNUMBER = '02354'; SELECT DISTINCT CASE WHEN ART_LOOKUP.ARL_KIND IN (1, 2) THEN SUPPLIERS.SUP_BRAND WHEN ART_LOOKUP.ARL_KIND IN (3, 4) THEN BRANDS.BRA_BRAND WHEN ART_LOOKUP.ARL_KIND = 5 THEN 'EAN' ELSE '' END AS BRAND, ART_LOOKUP.ARL_SEARCH_NUMBER AS NUMBERPARTS, ART_LOOKUP.ARL_KIND, ART_LOOKUP.ARL_ART_ID, DES_TEXTS.TEX_TEXT AS ART_COMPLETE_DES_TEXT FROM ART_LOOKUP LEFT JOIN BRANDS ON BRANDS.BRA_ID = ART_LOOKUP.ARL_BRA_ID INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_ART_ID INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID INNER JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = ARTICLES.ART_COMPLETE_DES_ID INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID WHERE ART_LOOKUP.ARL_SEARCH_NUMBER = CLEAN_NUMBER(@SEARCHNUMBER) AND /* I clear the search number of additional characters */ ART_LOOKUP.ARL_KIND IN (1, 2, 3, 4, 5) AND DESIGNATIONS.DES_LNG_ID = @LNGID GROUP BY BRAND, NUMBERPARTS -- Search analogues according to search numbers and brand (brand found in the previous chapter) SET @LNGID=4; -- by default English language SET @SEARCHNUMBER = '02354'; SET @SEARCHBRAND = 'METALCAUCHO'; SELECT DISTINCT IF (ART_LOOKUP2.ARL_KIND = 3, BRANDS2.BRA_BRAND, SUPPLIERS2.SUP_BRAND) AS BRAND, IF (ART_LOOKUP2.ARL_KIND IN (2, 3), ART_LOOKUP2.ARL_DISPLAY_NR, ARTICLES2.ART_ARTICLE_NR) AS NUMBER, ART_LOOKUP2.ARL_KIND FROM ART_LOOKUP LEFT JOIN BRANDS ON BRANDS.BRA_ID = ART_LOOKUP.ARL_BRA_ID INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_ART_ID INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID INNER JOIN ART_LOOKUP AS ART_LOOKUP2 FORCE KEY (PRIMARY) ON ART_LOOKUP2.ARL_ART_ID = ART_LOOKUP.ARL_ART_ID LEFT JOIN BRANDS AS BRANDS2 ON BRANDS2.BRA_ID = ART_LOOKUP2.ARL_BRA_ID INNER JOIN ARTICLES AS ARTICLES2 ON ARTICLES2.ART_ID = ART_LOOKUP2.ARL_ART_ID INNER JOIN SUPPLIERS AS SUPPLIERS2 FORCE KEY (PRIMARY) ON SUPPLIERS2.SUP_ID = ARTICLES2.ART_SUP_ID WHERE ART_LOOKUP.ARL_SEARCH_NUMBER = CLEAN_NUMBER(@SEARCHNUMBER) AND (ART_LOOKUP.ARL_KIND IN (3, 4) AND BRANDS.BRA_BRAND = @SEARCHBRAND OR ART_LOOKUP.ARL_KIND IN (1, 2) AND SUPPLIERS.SUP_BRAND = @SEARCHBRAND) AND (ART_LOOKUP.ARL_KIND, ART_LOOKUP2.ARL_KIND) IN ((1, 1), (1, 2), (1, 3), (2, 1), (2, 2), (2, 3), (3, 1), (3, 2), (3, 3), (4, 1)) ORDER BY BRAND, NUMBER 2.7. Showing images which used to parts SET @ARTID = 1050102; /* 24.5262-0575.3 [ATE] - Brake hose */ SELECT CONCAT(GRA_HOST, GRA_MAINFOLDER, GRA_SUPID, '/', GRA_ARTID, GRA_PREFICS, GRA_EXT) AS PATHIMAGE FROM GRAFORARTICLES WHERE (GRA_ARTID=@ARTID); 2.8. Display Specifications Parts, search sets checking entering set or kit SET @ARTID = 1050102; /* 24.5262-0575.3 [ATE] - Brake hose */ SELECT ALI_ART_ID, ALI_ART_ID_COMPONENT, ALI_QUANTITY FROM ARTICLE_LISTS WHERE (ALI_ART_ID=@ARTID OR (ALI_ART_ID_COMPONENT=@ARTID) ORDER BY ALI_SORT 2.9. Display the list of old or replaced numbers SET @ARTID = 1050102; /* 24.5262-0575.3 [ATE] - Brake hose */ SELECT DISTINCT ARTICLES.ART_SUP_ID AS IDBRAND, SUPERSEDED_ARTICLES.SUA_ART_ID AS OLDARTID, SUPERSEDED_ARTICLES.SUA_NUMBER AS NEWNUMBERPART FROM SUPERSEDED_ARTICLES LEFT JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_ART_ID WHERE SUA_ART_ID = @ARTID 2.10. TABLE OF PRICE LIST -- Search analogues according to search numbers and brand -- continued to item to 2.6. -- SET @LNGID=4; -- by default English language SET @SEARCHNUMBER = '02354'; SET @SEARCHBRAND = 'METALCAUCHO'; SELECT DISTINCT IF (ART_LOOKUP2.ARL_KIND = 3, BRANDS2.BRA_ID, SUPPLIERS2.SUP_ID) AS IDBRAND, IF (ART_LOOKUP2.ARL_KIND IN (2, 3), ART_LOOKUP2.ARL_DISPLAY_NR, ARTICLES2.ART_ARTICLE_NR) AS NUMBER FROM ART_LOOKUP LEFT JOIN BRANDS ON BRANDS.BRA_ID = ART_LOOKUP.ARL_BRA_ID INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP.ARL_ART_ID INNER JOIN SUPPLIERS ON SUPPLIERS.SUP_ID = ARTICLES.ART_SUP_ID INNER JOIN ART_LOOKUP AS ART_LOOKUP2 FORCE KEY (PRIMARY) ON ART_LOOKUP2.ARL_ART_ID = ART_LOOKUP.ARL_ART_ID LEFT JOIN BRANDS AS BRANDS2 ON BRANDS2.BRA_ID = ART_LOOKUP2.ARL_BRA_ID INNER JOIN ARTICLES AS ARTICLES2 ON ARTICLES2.ART_ID = ART_LOOKUP2.ARL_ART_ID INNER JOIN SUPPLIERS AS SUPPLIERS2 FORCE KEY (PRIMARY) ON SUPPLIERS2.SUP_ID = ARTICLES2.ART_SUP_ID WHERE ART_LOOKUP.ARL_SEARCH_NUMBER = CLEAN_NUMBER(@SEARCHNUMBER) AND (ART_LOOKUP.ARL_KIND IN (3, 4) AND BRANDS.BRA_BRAND = @SEARCHBRAND OR ART_LOOKUP.ARL_KIND IN (1, 2) AND SUPPLIERS.SUP_BRAND = @SEARCHBRAND) AND (ART_LOOKUP.ARL_KIND, ART_LOOKUP2.ARL_KIND) IN ((1, 1), (1, 2), (1, 3), (2, 1), (2, 2), (2, 3), (3, 1), (3, 2), (3, 3), (4, 1)) ORDER BY IDBRAND, NUMBER -- on output we will have the table of brands and number parts (tables have prices not for all products) SET @LISTSEARCHPARTS=((IDBRAND1, NUMBER1), (IDBRAND2, NUMBER2), (IDBRAND3, NUMBER3), (IDBRAND4, NUMBER4), ...); SET @LNGID=4; -- by default English language SELECT DISTINCT PRI_ART_ID, DES_TEXTS.TEX_TEXT AS NAMEPARTS, (SELECT BRA_BRAND FROM BRANDS WHERE BRA_ID=ART_SUP_ID) AS BRAND, ART_ARTICLE_NR, PRI_PRICE, PRI_CURRENCY_CODE FROM PRICES INNER JOIN ARTICLES ON (ART_ID = PRI_ART_ID) INNER JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = ART_COMPLETE_DES_ID AND DESIGNATIONS.DES_LNG_ID = @LNGID INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID WHERE (ART_SUP_ID, ART_ARTICLE_NR) IN ((@LISTSEARCHPARTS)) -- FOR EXAMPLE ((ART_SUP_ID, ART_ARTICLE_NR) IN ((13, '23159'),(13, '23160'))) ORDER BY NAMEPARTS, BRAND, ART_ARTICLE_NR; 3. Creating catalog 3.1. Links table SPARE PARTS-> CAR or CAR-> SPARE PARTS -- table with respect to Parts SET @ARTID = 1050102; /* 24.5262-0575.3 [ATE] - Brake hose */ SELECT LA_ART_ID, LAT_TYP_ID, LA_ID FROM LINK_ART INNER JOIN LINK_LA_TYP ON LAT_LA_ID = LA_ID WHERE LA_ART_ID = @ARTID -- table with respect to cars SET @TYPID=9045; -- for example its BMW 3 (E46) 320 d SELECT LAT_TYP_ID, LA_ART_ID, LAT_LA_ID FROM LINK_LA_TYP INNER JOIN LINK_ART ON LA_ID = LAT_LA_ID WHERE LAT_TYP_ID = @TYPID 3.2. Creating tree product categories SET @TYPID=9045; -- for example its BMW 3 (E46) 320 d SET @LNGID=4; -- by default English language SET @STRTYPE=1; -- type of product group /*1 - passenger cars 2 - truck cars 3 - engine 4 - universal 5 - axle*/ SELECT DISTINCT SEARCH_TREE.STR_LEVEL, ELT(SEARCH_TREE.STR_LEVEL, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT, ELT(SEARCH_TREE.STR_LEVEL, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID, ELT(SEARCH_TREE.STR_LEVEL-1, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT2, ELT(SEARCH_TREE.STR_LEVEL-1, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID2, ELT(SEARCH_TREE.STR_LEVEL-2, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT3, ELT(SEARCH_TREE.STR_LEVEL-2, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID3, ELT(SEARCH_TREE.STR_LEVEL-3, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT4, ELT(SEARCH_TREE.STR_LEVEL-3, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID4, ELT(SEARCH_TREE.STR_LEVEL-4, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT5, ELT(SEARCH_TREE.STR_LEVEL-4, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID5 FROM SEARCH_TREE JOIN LINK_GA_STR ON (SEARCH_TREE.STR_ID=LINK_GA_STR.LGS_STR_ID) JOIN LINK_LA_TYP ON (LINK_GA_STR.LGS_GA_ID=LINK_LA_TYP.LAT_GA_ID) LEFT JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = SEARCH_TREE.STR_DES_ID AND DESIGNATIONS.DES_LNG_ID = @LNGID LEFT JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID LEFT JOIN SEARCH_TREE AS SEARCH_TREE2 ON SEARCH_TREE2.STR_ID = SEARCH_TREE.STR_ID_PARENT LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = SEARCH_TREE2.STR_DES_ID AND DESIGNATIONS2.DES_LNG_ID = @LNGID LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID LEFT JOIN SEARCH_TREE AS SEARCH_TREE3 ON SEARCH_TREE3.STR_ID = SEARCH_TREE2.STR_ID_PARENT LEFT JOIN DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID = SEARCH_TREE3.STR_DES_ID AND DESIGNATIONS3.DES_LNG_ID = @LNGID LEFT JOIN DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS3.DES_TEX_ID LEFT JOIN SEARCH_TREE AS SEARCH_TREE4 ON SEARCH_TREE4.STR_ID = SEARCH_TREE3.STR_ID_PARENT LEFT JOIN DESIGNATIONS AS DESIGNATIONS4 ON DESIGNATIONS4.DES_ID = SEARCH_TREE4.STR_DES_ID AND DESIGNATIONS4.DES_LNG_ID = @LNGID LEFT JOIN DES_TEXTS AS DES_TEXTS4 ON DES_TEXTS4.TEX_ID = DESIGNATIONS4.DES_TEX_ID LEFT JOIN SEARCH_TREE AS SEARCH_TREE5 ON SEARCH_TREE5.STR_ID = SEARCH_TREE4.STR_ID_PARENT LEFT JOIN DESIGNATIONS AS DESIGNATIONS5 ON DESIGNATIONS5.DES_ID = SEARCH_TREE5.STR_DES_ID AND DESIGNATIONS5.DES_LNG_ID = @LNGID LEFT JOIN DES_TEXTS AS DES_TEXTS5 ON DES_TEXTS5.TEX_ID = DESIGNATIONS5.DES_TEX_ID WHERE LAT_TYP_ID=@TYPID AND SEARCH_TREE.STR_TYPE=@STRTYPE ORDER BY STR_TEXT, STR_TEXT2, STR_TEXT3, STR_TEXT4, STR_TEXT5 3.2.1. Selecting a list of spare parts according to the selected position the product categories SET @TYPID=9045; -- for example its BMW 3 (E46) 320 d SET @STRID=10618; /* flywheel */ SELECT LA_ART_ID, LA_ID /* applicability to the vehicle group to parts */ FROM LINK_GA_STR INNER JOIN LINK_LA_TYP ON (LAT_TYP_ID = @TYPID AND LAT_GA_ID = LGS_GA_ID) INNER JOIN LINK_ART ON LA_ID = LAT_LA_ID WHERE LGS_STR_ID <=> @STRID ORDER BY LA_ART_ID 3.3. Create a list of product groups SET @TYPID=9045; -- for example its BMW 3 (E46) 320 d SET @LNGID=4; -- by default English language SELECT LAT_TYP_ID, LA_ART_ID, LAT_LA_ID, GA_ID, (SELECT DES_TEXTS1.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS1 JOIN DESIGNATIONS AS DESIGNATIONS1 ON (DES_TEXTS1.TEX_ID = DESIGNATIONS1.DES_TEX_ID) WHERE (DESIGNATIONS1.DES_ID = GENERIC_ARTICLES.GA_DES_ID) AND (DESIGNATIONS1.DES_LNG_ID=@LNGID) ) AS GANAME, (SELECT DES_TEXTS2.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS2 JOIN DESIGNATIONS AS DESIGNATIONS2 ON (DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID) WHERE (DESIGNATIONS2.DES_ID = GENERIC_ARTICLES.GA_DES_ID_STANDARD) AND (DESIGNATIONS2.DES_LNG_ID=@LNGID) ) AS GASTANDARD, (SELECT DES_TEXTS3.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS3 JOIN DESIGNATIONS AS DESIGNATIONS3 ON (DES_TEXTS3.TEX_ID = DESIGNATIONS3.DES_TEX_ID) WHERE (DESIGNATIONS3.DES_ID = GENERIC_ARTICLES.GA_DES_ID_ASSEMBLY) AND (DESIGNATIONS3.DES_LNG_ID=@LNGID) ) AS GAASSEMBLY, (SELECT DES_TEXTS4.TEX_TEXT FROM DES_TEXTS AS DES_TEXTS4 JOIN DESIGNATIONS AS DESIGNATIONS4 ON (DES_TEXTS4.TEX_ID = DESIGNATIONS4.DES_TEX_ID) WHERE (DESIGNATIONS4.DES_ID = GENERIC_ARTICLES.GA_DES_ID_INTENDED) AND (DESIGNATIONS4.DES_LNG_ID=@LNGID) ) AS GAINTENDED FROM LINK_LA_TYP INNER JOIN LINK_ART ON LA_ID = LAT_LA_ID LEFT JOIN GENERIC_ARTICLES ON (GENERIC_ARTICLES.GA_ID=LINK_ART.LA_GA_ID) WHERE LAT_TYP_ID = @TYPID ORDER BY GAASSEMBLY, GANAME 3.3.1. Selecting a list of spare parts according to the selected position of commodity groups SET @TYPID=9045; -- for example its BMW 3 (E46) 320 d SET @GAID=577; /* flywheel */ SELECT DISTINCT LA_ART_ID, LA_ID /* applicability to the vehicle group to parts */ FROM LINK_GA_STR INNER JOIN LINK_LA_TYP ON (LAT_TYP_ID = @TYPID AND LAT_GA_ID = LGS_GA_ID) INNER JOIN LINK_ART ON LA_ID = LAT_LA_ID WHERE LGS_GA_ID <=> @GAID ORDER BY LA_ART_ID 3.4. Criteria group applicability to the vehicle group to parts SET @LAID=3455937; SET @LNGID=4; -- by default English language SELECT DES_TEXTS.TEX_TEXT AS CRITERIA_DES_TEXT, IFNULL(DES_TEXTS2.TEX_TEXT, LAC_VALUE) AS CRITERIA_VALUE_TEXT FROM LA_CRITERIA LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = LAC_KV_DES_ID LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID LEFT JOIN CRITERIA ON CRI_ID = LAC_CRI_ID LEFT JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = CRI_DES_ID LEFT JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID WHERE LAC_LA_ID = @LAID AND (DESIGNATIONS.DES_LNG_ID IS NULL OR DESIGNATIONS.DES_LNG_ID = @LNGID) AND (DESIGNATIONS2.DES_LNG_ID IS NULL OR DESIGNATIONS2.DES_LNG_ID = @LNGID) ORDER BY LA_CRITERIA.LAC_SORT 3.5. Showing more information group applicability to the vehicle group to parts SET @LAID=3455937; SET @LNGID=4; -- by default English language SELECT TMT_TEXT AS INFOTEXT FROM LA_INFO INNER JOIN TEXT_MODULES ON TMO_ID = LIN_TMO_ID INNER JOIN TEXT_MODULE_TEXTS ON TMT_ID = TMO_TMT_ID WHERE LIN_LA_ID = @LAID AND TMO_LNG_ID = @LNGID ORDER BY INFOTEXT