En ART LOOKUP2.sql

/* Analog search (original/non-original/commercial) to any specified number (original/non-original/commercial) */

/* BRAND - Product supplier name */

/* NUMBER - Product number */

/* ARL_KIND - Product number kind: 1 - Non-originalal, 2 - Commercial, 3 - Original */

/* */

There are simplified number variations at the input here, and already full ones at the output (with spaces/points/dashes etc.) */

/* */

/* WARNING: Do not forget 'single quotes' during values substitution instead of the variable @NUMBER, otherwise this query is running VERY slowly, as it does not use the text index by the field ARL_SEARCH_NUMBER */

SET @NUMBER = '1244211212'; /* Brake disk */
SET @BRAND = 'MERCEDES-BENZ';

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 = @NUMBER AND	(ART_LOOKUP.ARL_KIND IN (3, 4) AND BRANDS.BRA_BRAND = @BRAND OR	 SUPPLIERS.SUP_BRAND = @BRAND) 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;

• Back to index page: Tecdoc_queries_and_structure_manual_-_version_2

• Prices and packages for Irbis software based on tecdoc from Mstarproject company: http://www.mstarproject.com/en/?action=tecdoc_mysql_site