New description file to create tables and sql-queries
Новый файл описания создания таблиц и sql-запросов
http://ttc.bovsoft.com/download/example/democatalog/readme.txt
-------------------------



1. CARS
1.1. Select car brand
1.2. Select specified car brand
1.3. Grouping vehicles specified brand by model and dates of issue, for example "A5 (8T) [06/2007 - TODAY]"
1.4. Select cars that released in the specified year


2. SPARE PARTS
2.1. Select brand parts
2.2. Selecting parts specified brand
2.3. Grouping name parts specified brand names
2.4. Search for parts by specified trade code
2.5. Search for parts specified by the original code or trade code of other manufacturers are not included in the main table parts (cross codes)
2.5.1. Search brand for original code or trade code of other manufacturers are not included in the main table parts
2.5.2. Search specification list for the specified spare parts (what is in the set)
2.6.1. Applicability of spare parts for cars. Using linked table EXPORTADDITION_TYPID_ARTID
2.6.2. Applicability of spare parts for cars. Using linked table LinkedTableParts і LinkedTableCars
2.7. Choosing analogues specified parts shorted (selection of spare parts which are analogues listed in "http://goo.gl/nEp0S")
2.8. Choosing analogues specified parts expanded (selection of spare parts which are analogues relatively original numbers)
2.9. Sample function to clear the entered number of all characters except the English letters and digits



3. CREATE CATALOG
3.1. Using grouped table [TableUsed...]
3.1.1. Choosing a car
3.1.2. Show product groups of parts for specified  car
3.1.3. Choosing spare parts that correspond to specified groups parts
3.2. Using linked table LinkedTableParts, LinkedTableCars, TableLinkGroupsCarsAndParts, TableTreeProductGroup
3.2.1. Sequential scan of the list of product groups
3.2.2. Show tree list the category groups trade
3.2.3. Selecting spare parts that used for the selected category group trade 
3.3. Using linked table EXPORTADDITION_TYPID_ARTID, EXPORTADDITION_TYPID_SRTID, EXPORTADDITION_ARTID_SRTID, TableTreeProductGroup
3.3.1. Show tree list the category groups trade
3.3.2. Selecting spare parts that used for the selected category group trade 

4. KBA / MIME / NUMBERPLATE
4.1. Search for a car to the specified KBA or MIME or NUMBERPLATE code

===============================
1.1. Select car brand

SELECT 
	CAR_BRANDS 
FROM TABLECARS
GROUP BY CAR_BRANDS;

===============================
1.2. Select specified car brand

SET @BRAND='BMW';

SELECT 
	*
FROM TABLECARS
WHERE 
	CAR_BRANDS=@BRAND		
ORDER BY 
	MODEL_CAR, TYP_CAR, OF_THE_YEAR;

===============================
1.3. Grouping vehicles specified brand by model and dates of issue, for example "A5 (8T) [06/2007 - TODAY]"

SET @BRAND='BMW';

SELECT 
	MODEL_CAR, 
	MIN(OF_THE_YEAR),
	CASE
	  WHEN ((MAX(UP_TO_A_YEAR)=0) OR (MAX(UP_TO_A_YEAR) IS NULL))
	  THEN 'TODAY'
	  ELSE	MAX(UP_TO_A_YEAR)
	END
FROM TABLECARS
WHERE 
	CAR_BRANDS=@BRAND		
GROUP BY 
	MODEL_CAR;

===============================
1.4. Select cars that released in the specified year

SET @YEAR=2000;

SELECT 
	*
FROM TABLECARS
WHERE 
	(TRUNCATE(OF_THE_YEAR/100, 0)>=@YEAR)
	AND ((TRUNCATE(UP_TO_A_YEAR/100, 0)<=@YEAR) OR ((UP_TO_A_YEAR IS NULL) OR (UP_TO_A_YEAR=0)))
ORDER BY 
	CAR_BRANDS, MODEL_CAR, TYP_CAR, OF_THE_YEAR;

===============================
2.1. Select brand parts

SELECT 
	BRAND
FROM MAINTABLEPARTS
GROUP BY BRAND;

===============================
2.2. Selecting parts specified brand

SET @BRAND='A.B.S.';

SELECT 
	*
FROM MAINTABLEPARTS
WHERE 
	BRAND=@BRAND		
ORDER BY 
	BRAND, NAME_PARTS, CODE_PARTS_ADVANCED;

===============================
2.3. Grouping name parts specified brand names

SET @BRAND='A.B.S.';

SELECT 
	NAME_PARTS
FROM MAINTABLEPARTS
WHERE 
	BRAND=@BRAND		
GROUP BY 
	NAME_PARTS;

===============================
2.4. Search for parts by specified trade code

SET @NUMBER1='032 145 276';
SET @NUMBER2=CLEANNUMBER(@NUMBER1);  /* see function 2.9. */

SELECT 
	TTC_ART_ID,
	BRAND,
	CODE_PARTS_ADVANCED  /* be shown extended trade code, for example : ME-BJ-6313 */
FROM MAINTABLEPARTS
WHERE 
	(CODE_PART=@NUMBER2) OR (CODE_PARTS_ADVANCED=@NUMBER1)
ORDER BY 
	BRAND, CODE_PARTS_ADVANCED;

===============================
2.5. Search for parts specified by the original code or trade code of other manufacturers are not included in the main table parts (cross codes)

SET @NUMBER='220 352 02 27';
SET @NUMBER=CLEANNUMBER(@NUMBER);  /* see function 2.9. */
/* output @NUMBER : 2203520227 */
SET @BRAND='MERCEDES'; /* provided that we know the brand of parts, or see 2.5.1. */

SELECT 
	TTC_ART_ID,
	BRAND,
	CODE_PARTS_ADVANCED  /* be shown extended trade code, for example : ME-BJ-6313 */
FROM MAINTABLEPARTS
WHERE 
	TTC_ART_ID IN (SELECT TTC_ART_ID 
			  FROM TABLEPARTSCROSSREFERENCE
			  WHERE (BRANDS=@BRAND) AND (CODE_PARTS=@NUMBER) 
			 )
ORDER BY 
	BRAND, CODE_PARTS_ADVANCED;

/* ----- OR ----- */

SELECT 
	TTC_ART_ID,
	mainART_BRANDS,
	mainART_CODE_PARTS   /* be shown short trade code, for example : MEBJ6313 */
FROM TABLEPARTSCROSSREFERENCE
WHERE 
	(BRANDS=@BRAND) AND (CODE_PARTS=@NUMBER) 
ORDER BY 
	mainART_BRANDS,	mainART_CODE_PARTS;

===============================
2.5.1. Search brand for original code or trade code of other manufacturers are not included in the main table parts

SET @NUMBER='220 352 02 27';
SET @NUMBER=CLEANNUMBER(@NUMBER);  /* see function 2.9. */
/* output @NUMBER : 2203520227 */

SELECT 
	BRANDS
FROM TABLEPARTSCROSSREFERENCE
WHERE 
	(CODE_PARTS=@NUMBER) 
GROUP BY 
	BRANDS;

===============================
2.5.2. Search specification list for the specified spare parts (what is in the set)

SET @NUMBER='ME-BJ-6313';
SET @NUMBER=CLEANNUMBER(@NUMBER);  /* see function 2.9. */
/* output @NUMBER : MEBJ6313 */
SET @BRAND='MOOG';

SELECT 
	BRANDS,
	CODE_PARTS,
	QUANTITY
FROM TABLESPECIFICATIONSPARTS
WHERE 
	(mainART_CODE_PARTS=@NUMBER) 
	AND (mainART_BRANDS=@BRAND)	
ORDER BY 
	BRANDS,	CODE_PARTS, QUANTITY;

/* ----- OR in the case of knowledge value TTC_ART_ID ----- */
SET @ARTID=1694029;

SELECT 
	BRANDS,
	CODE_PARTS,
	QUANTITY
FROM TABLESPECIFICATIONSPARTS
WHERE 
	(mainTTC_ART_ID=ARTID) 	
ORDER BY 
	BRANDS,	CODE_PARTS, QUANTITY;

===============================
2.6.1. Applicability of spare parts for cars. Using linked table EXPORTADDITION_TYPID_ARTID

/* finding value TTC_ART_ID of tables "MAINTABLEPARTS", for example as in 2.4. and/or 2.5. */
SET @ARTID=1014923; /* MANN-FILTER : CU2425, ver "http://goo.gl/nEp0S" 1/2013 */

SELECT 
	*
FROM TABLECARS
WHERE 
	TTC_TYP_ID in (SELECT TTC_TYP_ID
			  FROM EXPORTADDITION_TYPID_ARTID
			  WHERE (TTC_ART_ID=@ARTID)
			  )
ORDER BY 
	CAR_BRANDS, MODEL_CAR, TYP_CAR, OF_THE_YEAR;

===============================
2.6.2. Applicability of spare parts for cars. Using linked table LinkedTableParts і LinkedTableCars

/* finding value TTC_ART_ID of tables "MAINTABLEPARTS", for example as in 2.4. and/or 2.5. */
SET @ARTID=1014923; /* MANN-FILTER : CU2425, ver "http://goo.gl/nEp0S" 1/2013 */

SELECT 
	*
FROM TABLECARS
WHERE 
	TTC_TYP_ID in (SELECT LAT_TYP_ID
			  FROM LINKEDTABLECARS
			  WHERE LAT_LA_ID IN (SELECT LA_ID
					      FROM LINKEDTABLEPARTS
					      WHERE (LA_ART_ID=@ARTID)
					      GROUP BY LA_ID
					      )
			  GROUP BY LAT_TYP_ID
			  )
ORDER BY 
	CAR_BRANDS, MODEL_CAR, TYP_CAR, OF_THE_YEAR;

===============================
2.7. Choosing analogues specified parts shorted (selection of spare parts which are analogues listed in "http://goo.gl/nEp0S")

/* finding value TTC_ART_ID of tables "MAINTABLEPARTS", for example as in 2.4. and/or 2.5. */
SET @ARTID=1014923; /* MANN-FILTER : CU2425, ver "http://goo.gl/nEp0S" 1/2013 */

SELECT 
	BRANDS,
	CODE_PARTS   /* be shown short trade code, for example : KNECHT : LA41 */
FROM TABLEPARTSCROSSREFERENCE
WHERE 
	(TTC_ART_ID=@ARTID) 
	AND (KIND IN (2, 3, 4))  <---   /* additional filter selection
					2 - user number
					3 - original number
					4 - no original number (Trade number) */
ORDER BY 
	BRANDS,	CODE_PARTS;

===============================
2.8. Choosing analogues specified parts expanded (selection of spare parts which are analogues relatively original numbers)

/* finding value TTC_ART_ID of tables "MAINTABLEPARTS", for example as in 2.4. and/or 2.5. */
SET @ARTID=1014923; /* MANN-FILTER : CU2425, ver "http://goo.gl/nEp0S" 1/2013 */

SELECT 
	BRANDS,
	CODE_PARTS
FROM TABLEPARTSCROSSREFERENCE
WHERE 
	(TTC_ART_ID=@ARTID) 
	OR  (TTC_ART_ID IN (SELECT TTC_ART_ID 
				FROM TABLEPARTSCROSSREFERENCE
				WHERE ((BRANDS, CODE_PARTS) IN (SELECT BRANDS, CODE_PARTS
								FROM TABLEPARTSCROSSREFERENCE
								WHERE (TTC_ART_ID=@ARTID)
								AND (KIND=3)
								)
					)
				GROUP BY TTC_ART_ID
				)
		)
GROUP BY 
	BRANDS,	CODE_PARTS;

===============================
2.9. Sample function to clear the entered number of all characters except the English letters and digits

/* function to clear the entered number of all characters except the English letters and digits */
/* example for create this function */

DROP FUNCTION IF EXISTS CLEANNUMBER;
DELIMITER $$
CREATE FUNCTION CLEANNUMBER(STR VARCHAR(105))
RETURNS VARCHAR(105) DETERMINISTIC
BEGIN
	DECLARE LEN INT DEFAULT LENGTH(STR);
	DECLARE I INT DEFAULT 1;
	DECLARE NEWSTR VARCHAR(105) DEFAULT '';
	DECLARE C CHAR;

	WHILE I<=LEN DO
		SET C = SUBSTR(STR, I, 1);
		IF C >= 'a' AND C <= 'z' OR
		   C >= 'A' AND C <= 'Z' OR
		   C >= '0' AND C <= '9' THEN
			SET NEWSTR = CONCAT(NEWSTR, C);
		END IF;
		SET I = I+1;
	END WHILE;

	RETURN NEWSTR;
END$$
DELIMITER ;

/* example of use */
SET @NUMBER = 'ME-BJ-6313';
SET @NUMBER = CLEANNUMBER(@NUMBER);
SELECT @NUMBER; 
/* output @NUMBER : MEBJ6313 */

===============================
3.1.1. Choosing a car

/* determine value TTC_TYP_ID of table TABLECARS or similar value of table TABLEUSEDPARTSFORCARS */
/* TABLECARS */

SELECT
	TTC_TYP_ID,	
	CAR_BRANDS,
	MODEL_CAR,
	TYP_CAR,
	BODY_TYPE,
	OF_THE_YEAR,
	UP_TO_A_YEAR
FROM TABLECARS
WHERE < --- /* specify a filter to select the desired auto, for example : brand, year. See 1.1., 1.2., 1.3. */
ORDER BY 
	CAR_BRANDS, MODEL_CAR, TYP_CAR, OF_THE_YEAR;

/* ----- OR ----- */

SELECT
	TTC_TYP_ID,	
	CAR_BRAND,
	CAR_MODEL,
	CAR_TYP,
	CAR_BODY,
	CAR_OF_YEAR,
	CAR_TO_YEAR
FROM TABLEUSEDPARTSFORCARS
WHERE < --- /* specify a filter to select the desired auto, for example : brand, year. See 1.1., 1.2., 1.3. */
GROUP BY
	TTC_TYP_ID, CAR_BRAND, CAR_MODEL, CAR_TYP, CAR_BODY, CAR_OF_YEAR, CAR_TO_YEAR;

===============================
3.1.2. Show product groups of parts for specified  car

/* finding value TTC_TYP_ID, see 3.1.1. */
SET @TYPID=982;   /* OPEL VECTRA A (86_, 87_) 1.6 Saloon 198809-199305, ver "http://goo.gl/nEp0S" 1/2013 */

SELECT
	TTC_GA_ID,
	GA_NAME
FROM 
	TABLEUSEDPARTSFORCARS
WHERE
	(TTC_TYP_ID=@TYPID)
GROUP BY
	TTC_GA_ID, GA_NAME;

===============================
3.1.3. Choosing spare parts that correspond to specified groups parts

/* finding value TTC_GA_ID, see 3.1.2. */
SET @GAID=92;  

SELECT
	TTC_ART_ID, 
	ART_NAME,
	ART_BRAND,
	ART_CODE_2
FROM 
	TABLEUSEDPARTSFORCARS
WHERE
	(TTC_GA_ID=@GAID)
ORDER BY
	TTC_ART_ID, ART_NAME, ART_BRAND, ART_CODE_2;

===============================
3.2.1. Sequential scan of the list of product groups

/* finding value TTC_TYP_ID, see 3.1.1. */
SET @TYPID=982;   /* OPEL VECTRA A (86_, 87_) 1.6 Saloon 198809-199305, ver "http://goo.gl/nEp0S" 1/2013 */

SELECT
	STR_ID,
	STR_TEXT
FROM TABLETREEPRODUCTGROUP
WHERE  (STR_ID IN (SELECT LGS_STR_ID 
		   FROM TABLELINKGROUPSCARSANDPARTS
		   WHERE (LGS_GA_ID IN (SELECT LAT_GA_ID
					FROM LINKEDTABLECARS
					WHERE (LAT_TYP_ID=@TYPID)
					GROUP BY LAT_GA_ID
					)
			 )
		   GROUP BY LGS_STR_ID 
		   )
	)
	AND (STR_LEVEL=1)
ORDER BY
	STR_TEXT; 

/* Next, show subgroups for selected of product group STR_ID */
SET @STRID=10001; /* Passenger Cars */

SELECT
	STR_ID,
	STR_TEXT
FROM TABLETREEPRODUCTGROUP
WHERE  (STR_ID IN (SELECT LGS_STR_ID 
		   FROM TABLELINKGROUPSCARSANDPARTS
		   WHERE (LGS_GA_ID IN (SELECT LAT_GA_ID
					FROM LINKEDTABLECARS
					WHERE (LAT_TYP_ID=@TYPID)
					GROUP BY LAT_GA_ID
					)
			 )
		   GROUP BY LGS_STR_ID 
		   )
	)
	AND (STR_ID_PARENT=@STRID)
ORDER BY
	STR_TEXT; 

/* Next, show subgroups for selected of product group STR_ID (as in the previous example), until you select the desired product group, or when not there will be subgroups */
...

===============================
3.2.2. Show tree list the category groups trade

/* finding value TTC_TYP_ID, see 3.1.1. */
SET @TYPID=982;   /* OPEL VECTRA A (86_, 87_) 1.6 Saloon 198809-199305, ver "http://goo.gl/nEp0S" 1/2013 */

SELECT
	TABLETREEPRODUCTGROUP.STR_LEVEL,
	case
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=1 then TABLETREEPRODUCTGROUP.STR_TEXT
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=2 then TABLETREEPRODUCTGROUP2.STR_TEXT
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=3 then TABLETREEPRODUCTGROUP3.STR_TEXT
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=4 then TABLETREEPRODUCTGROUP4.STR_TEXT
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=5 then TABLETREEPRODUCTGROUP5.STR_TEXT else null
	end,
	case
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=1 then TABLETREEPRODUCTGROUP.STR_ID
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=2 then TABLETREEPRODUCTGROUP2.STR_ID
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=3 then TABLETREEPRODUCTGROUP3.STR_ID
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=4 then TABLETREEPRODUCTGROUP4.STR_ID
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=5 then TABLETREEPRODUCTGROUP5.STR_ID else null
	end,
	/* -------------------------------- */
	case
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=2 then TABLETREEPRODUCTGROUP.STR_TEXT
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=3 then TABLETREEPRODUCTGROUP2.STR_TEXT
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=4 then TABLETREEPRODUCTGROUP3.STR_TEXT
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=5 then TABLETREEPRODUCTGROUP4.STR_TEXT else null
	end,
	case
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=2 then TABLETREEPRODUCTGROUP.STR_ID
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=3 then TABLETREEPRODUCTGROUP2.STR_ID
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=4 then TABLETREEPRODUCTGROUP3.STR_ID
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=5 then TABLETREEPRODUCTGROUP4.STR_ID else null
	end,
	/* -------------------------------- */
	case
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=3 then TABLETREEPRODUCTGROUP.STR_TEXT
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=4 then TABLETREEPRODUCTGROUP2.STR_TEXT
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=5 then TABLETREEPRODUCTGROUP3.STR_TEXT else null
	end,
	case
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=3 then TABLETREEPRODUCTGROUP.STR_ID
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=4 then TABLETREEPRODUCTGROUP2.STR_ID
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=5 then TABLETREEPRODUCTGROUP3.STR_ID else null
	end,
	/* -------------------------------- */
	case
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=4 then TABLETREEPRODUCTGROUP.STR_TEXT
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=5 then TABLETREEPRODUCTGROUP2.STR_TEXT else null
	end,
	case
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=4 then TABLETREEPRODUCTGROUP.STR_ID
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=5 then TABLETREEPRODUCTGROUP2.STR_ID else null
	end,
	/* -------------------------------- */
	case
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=5 then TABLETREEPRODUCTGROUP.STR_TEXT else null
	end,
	case
	  when TABLETREEPRODUCTGROUP.STR_LEVEL=5 then TABLETREEPRODUCTGROUP.STR_ID else null
	end
FROM
	TABLETREEPRODUCTGROUP
	INNER JOIN TABLELINKGROUPSCARSANDPARTS ON (TABLETREEPRODUCTGROUP.STR_ID=TABLELINKGROUPSCARSANDPARTS.LGS_STR_ID)
	INNER JOIN LINKEDTABLECARS ON (LINKEDTABLECARS.LAT_GA_ID=TABLELINKGROUPSCARSANDPARTS.LGS_GA_ID)
	/* -------------------------------- */
	LEFT JOIN TABLETREEPRODUCTGROUP AS TABLETREEPRODUCTGROUP2 ON (TABLETREEPRODUCTGROUP2.STR_ID = TABLETREEPRODUCTGROUP.STR_ID_PARENT)
	INNER JOIN TABLELINKGROUPSCARSANDPARTS AS TABLELINKGROUPSCARSANDPARTS2 ON (TABLETREEPRODUCTGROUP2.STR_ID=TABLELINKGROUPSCARSANDPARTS2.LGS_STR_ID)
	INNER JOIN LINKEDTABLECARS AS LINKEDTABLECARS2 ON (LINKEDTABLECARS2.LAT_GA_ID=TABLELINKGROUPSCARSANDPARTS2.LGS_GA_ID)
	/* -------------------------------- */
	LEFT JOIN TABLETREEPRODUCTGROUP AS TABLETREEPRODUCTGROUP3 ON (TABLETREEPRODUCTGROUP3.STR_ID = TABLETREEPRODUCTGROUP2.STR_ID_PARENT)
	INNER JOIN TABLELINKGROUPSCARSANDPARTS AS TABLELINKGROUPSCARSANDPARTS3 ON (TABLETREEPRODUCTGROUP3.STR_ID=TABLELINKGROUPSCARSANDPARTS3.LGS_STR_ID)
	INNER JOIN LINKEDTABLECARS AS LINKEDTABLECARS3 ON (LINKEDTABLECARS3.LAT_GA_ID=TABLELINKGROUPSCARSANDPARTS3.LGS_GA_ID)
	/* -------------------------------- */
	LEFT JOIN TABLETREEPRODUCTGROUP AS TABLETREEPRODUCTGROUP4 ON (TABLETREEPRODUCTGROUP4.STR_ID = TABLETREEPRODUCTGROUP3.STR_ID_PARENT)
	INNER JOIN TABLELINKGROUPSCARSANDPARTS AS TABLELINKGROUPSCARSANDPARTS4 ON (TABLETREEPRODUCTGROUP4.STR_ID=TABLELINKGROUPSCARSANDPARTS4.LGS_STR_ID)
	INNER JOIN LINKEDTABLECARS AS LINKEDTABLECARS4 ON (LINKEDTABLECARS4.LAT_GA_ID=TABLELINKGROUPSCARSANDPARTS4.LGS_GA_ID)
	/* -------------------------------- */
	LEFT JOIN TABLETREEPRODUCTGROUP AS TABLETREEPRODUCTGROUP5 ON (TABLETREEPRODUCTGROUP5.STR_ID = TABLETREEPRODUCTGROUP4.STR_ID_PARENT)
	INNER JOIN TABLELINKGROUPSCARSANDPARTS AS TABLELINKGROUPSCARSANDPARTS5 ON (TABLETREEPRODUCTGROUP5.STR_ID=TABLELINKGROUPSCARSANDPARTS5.LGS_STR_ID)
	INNER JOIN LINKEDTABLECARS AS LINKEDTABLECARS5 ON (LINKEDTABLECARS5.LAT_GA_ID=TABLELINKGROUPSCARSANDPARTS5.LGS_GA_ID)
	/* -------------------------------- */
WHERE
	LINKEDTABLECARS.LAT_TYP_ID=@TYPID AND
	LINKEDTABLECARS2.LAT_TYP_ID=@TYPID AND
	LINKEDTABLECARS3.LAT_TYP_ID=@TYPID AND
	LINKEDTABLECARS4.LAT_TYP_ID=@TYPID AND
	LINKEDTABLECARS5.LAT_TYP_ID=@TYPID
GROUP BY
	TABLETREEPRODUCTGROUP.STR_LEVEL,
	TABLETREEPRODUCTGROUP.STR_TEXT,
	TABLETREEPRODUCTGROUP.STR_ID,
	TABLETREEPRODUCTGROUP2.STR_TEXT,
	TABLETREEPRODUCTGROUP2.STR_ID,
	TABLETREEPRODUCTGROUP3.STR_TEXT,
	TABLETREEPRODUCTGROUP3.STR_ID,
	TABLETREEPRODUCTGROUP4.STR_TEXT,
	TABLETREEPRODUCTGROUP4.STR_ID,
	TABLETREEPRODUCTGROUP5.STR_TEXT
	TABLETREEPRODUCTGROUP5.STR_ID,
ORDER BY
	TABLETREEPRODUCTGROUP.STR_TEXT,
	TABLETREEPRODUCTGROUP2.STR_TEXT,
	TABLETREEPRODUCTGROUP3.STR_TEXT,
	TABLETREEPRODUCTGROUP4.STR_TEXT,
	TABLETREEPRODUCTGROUP5.STR_TEXT;

===============================
3.2.3. Selecting spare parts that used for the selected category group trade 

/* TTC_TYP_ID, for which we find the appropriate product group STR_ID */
SET @TYPID=982;   /* OPEL VECTRA A (86_, 87_) 1.6 Saloon 198809-199305, ver "http://goo.gl/nEp0S" 1/2013 */
SET @STRID=10132; /* Brake Disc */

SELECT
	TTC_ART_ID,
	NAME_PARTS,
	BRAND,
	CODE_PARTS_ADVANCED
FROM MAINTABLEPARTS
WHERE 
	TTC_ART_ID IN (SELECT LA_ART_ID
			  FROM LINKEDTABLEPARTS
			  WHERE LA_ID IN (SELECT LAT_LA_ID
					  FROM LINKEDTABLECARS
					  WHERE LAT_GA_ID IN (SELECT LGS_GA_ID
							      FROM TABLELINKGROUPSCARSANDPARTS
							      WHERE (LGS_STR_ID=@STRID)
							      GROUP BY LGS_GA_ID
							      )
						AND (LAT_TYP_ID=@TYPID)
					  GROUP BY LAT_LA_ID
					  )
			  GROUP BY LA_ART_ID
			  )
ORDER BY
	NAME_PARTS, BRAND, CODE_PARTS_ADVANCED;
			
===============================
3.3.1. Show tree list the category groups trade

/* finding value TTC_TYP_ID, see 3.1.1. */
SET @TYPID=982;   /* OPEL VECTRA A (86_, 87_) 1.6 Saloon 198809-199305, ver "http://goo.gl/nEp0S" 1/2013 */

SELECT
	TEXT1,
	STR_ID1,
	TEXT2,
	STR_ID2,
	TEXT3,
	STR_ID3,
	TEXT4,
	STR_ID4,
	TEXT5,
	STR_ID5
FROM 
	EXPORTADDITION_TYPID_SRTID
WHERE
	TTC_TYP_ID=@TYPID
ORDER BY
	TEXT1,
	TEXT2,
	TEXT3,
	TEXT4,
	TEXT5;

/* ----- OR ----- */

SELECT
	(SELECT STR_TEXT FROM TABLETREEPRODUCTGROUP WHERE STR_ID=STR_ID1) AS TEXT1,
	STR_ID1,
	(SELECT STR_TEXT FROM TABLETREEPRODUCTGROUP WHERE STR_ID=STR_ID2) AS TEXT2,
	STR_ID2,
	(SELECT STR_TEXT FROM TABLETREEPRODUCTGROUP WHERE STR_ID=STR_ID3) AS TEXT3,
	STR_ID3,
	(SELECT STR_TEXT FROM TABLETREEPRODUCTGROUP WHERE STR_ID=STR_ID4) AS TEXT4,
	STR_ID4,
	(SELECT STR_TEXT FROM TABLETREEPRODUCTGROUP WHERE STR_ID=STR_ID5) AS TEXT5,
	STR_ID5
FROM 
	EXPORTADDITION_TYPID_SRTID
WHERE
	TTC_TYP_ID=@TYPID
ORDER BY
	TEXT1,
	TEXT2,
	TEXT3,
	TEXT4,
	TEXT5;
		
===============================
3.3.2. Selecting spare parts that used for the selected category group trade 

/* TTC_TYP_ID, for which we find the appropriate product group STR_ID */
SET @TYPID=982;   /* OPEL VECTRA A (86_, 87_) 1.6 Saloon 198809-199305, ver "http://goo.gl/nEp0S" 1/2013 */
SET @STRID=10132; /* Brake Disc */

SELECT
        TTC_ART_ID,
        NAME_PARTS,
        BRAND,
        CODE_PARTS_ADVANCED
FROM MAINTABLEPARTS
WHERE
        TTC_ART_ID IN (SELECT TTC_ART_ID
                         FROM EXPORTADDITION_TYPID_ARTID
                         WHERE TTC_TYP_ID=@TYPID
                         )
        AND TTC_ART_ID  IN  (SELECT TTC_ART_ID
                                FROM EXPORTADDITION_ARTID_SRTID
                                WHERE STR_ID=@STRID
                                )
ORDER BY
        NAME_PARTS, BRAND, CODE_PARTS_ADVANCED;

===============================
4.1. Search for a car to the specified KBA or MIME or NUMBERPLATE code

SET @KEYNUMBER='0588ATX';  /* for example KBA-NUMBER */

SELECT
	*
FROM TABLECARS
WHERE
	(TTC_TYP_ID IN (SELECT TTC_TYP_ID FROM EXPORTADDITION_KBA WHERE KEYNUMBER=@KEYNUMBER))		/* for German */
	OR (TTC_TYP_ID IN (SELECT TTC_TYP_ID FROM EXPORTADDITION_MIME WHERE KEYNUMBER=@KEYNUMBER))	/* for France */
	OR (TTC_TYP_ID IN (SELECT TTC_TYP_ID FROM EXPORTADDITION_NPLATE WHERE KEYNUMBER=@KEYNUMBER));	/* for Netherlands */