T8SLJVrP

1the union of two queries
2SELECT
3    ART_ARTICLE_NR,
4    SUP_BRAND,
5    DES_TEXTS.TEX_TEXT AS ART_COMPLETE_DES_TEXT,
6    DES_TEXTS2.TEX_TEXT AS ART_STATUS_TEXT
7FROM ARTICLES
8    INNER JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = ART_COMPLETE_DES_ID
9    INNER JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID
10    INNER JOIN SUPPLIERS ON SUP_ID = ART_SUP_ID
11    INNER JOIN ART_COUNTRY_SPECIFICS ON ACS_ART_ID = ART_ID
12    INNER JOIN DESIGNATIONS 
13        AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = ACS_KV_STATUS_DES_ID
14    INNER JOIN DES_TEXTS
15        AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID
16WHERE
17    ART_ID IN ( 
18                52277
19        ) AND
20    DESIGNATIONS.DES_LNG_ID = 19 AND
21    DESIGNATIONS2.DES_LNG_ID = 19;
22	
23SELECT
24    DES_TEXTS.TEX_TEXT AS CRITERIA_DES_TEXT,
25    IFNULL(DES_TEXTS2.TEX_TEXT, ACR_VALUE) AS CRITERIA_VALUE_TEXT
26FROM
27    ARTICLE_CRITERIA
28    LEFT JOIN DESIGNATIONS
29        AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = ACR_KV_DES_ID
30    LEFT JOIN DES_TEXTS
31        AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID
32    LEFT JOIN CRITERIA ON CRI_ID = ACR_CRI_ID
33    LEFT JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = CRI_DES_ID
34    LEFT JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID
35WHERE
36    ACR_ART_ID IN (
37            52277
38        ) AND
39    (DESIGNATIONS.DES_LNG_ID IS NULL OR DESIGNATIONS.DES_LNG_ID = 19) AND
40        (DESIGNATIONS2.DES_LNG_ID IS NULL OR DESIGNATIONS2.DES_LNG_ID = 19);
41	
42CREATE TABLE IF NOT EXISTS articles ( ART_ID int(11) NOT NULL, ART_ARTICLE_NR varchar(66) NOT NULL, ART_SUP_ID smallint(6) DEFAULT NULL, ART_DES_ID int(11) DEFAULT NULL, ART_COMPLETE_DES_ID int(11) DEFAULT NULL, ART_PACK_SELFSERVICE smallint(6) DEFAULT NULL, ART_MATERIAL_MARK smallint(6) DEFAULT NULL, ART_REPLACEMENT smallint(6) DEFAULT NULL, ART_ACCESSORY smallint(6) DEFAULT NULL, ART_BATCH_SIZE1 int(11) DEFAULT NULL, ART_BATCH_SIZE2 int(11) DEFAULT NULL, PRIMARY KEY (ART_ID), KEY ART_ARTICLE_NR (ART_ARTICLE_NR(10)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
43
44CREATE TABLE IF NOT EXISTS article_criteria ( ACR_ART_ID int(11) NOT NULL, ACR_GA_ID int(11) NOT NULL, ACR_SORT smallint(6) NOT NULL, ACR_CRI_ID smallint(6) NOT NULL, ACR_VALUE varchar(60) DEFAULT NULL, ACR_KV_DES_ID int(11) DEFAULT NULL, ACR_DISPLAY smallint(6) DEFAULT NULL, PRIMARY KEY (ACR_ART_ID,ACR_GA_ID,ACR_SORT) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
45
46CREATE TABLE IF NOT EXISTS art_country_specifics ( ACS_ART_ID int(11) NOT NULL, ACS_PACK_UNIT int(11) DEFAULT NULL, ACS_QUANTITY_PER_UNIT int(11) DEFAULT NULL, ACS_KV_STATUS_DES_ID int(11) DEFAULT NULL, ACS_KV_STATUS varchar(9) DEFAULT NULL, ACS_STATUS_DATE datetime DEFAULT NULL, KEY ACS_ART_ID (ACS_ART_ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
47
48CREATE TABLE IF NOT EXISTS criteria ( CRI_ID smallint(6) NOT NULL, CRI_DES_ID int(11) NOT NULL, CRI_SHORT_DES_ID int(11) DEFAULT NULL, CRI_UNIT_DES_ID int(11) DEFAULT NULL, CRI_TYPE binary(1) NOT NULL, CRI_KT_ID smallint(6) DEFAULT NULL, CRI_IS_INTERVAL smallint(6) DEFAULT NULL, CRI_SUCCESSOR smallint(6) DEFAULT NULL, PRIMARY KEY (CRI_ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
49
50CREATE TABLE IF NOT EXISTS designations ( DES_ID int(11) NOT NULL, DES_LNG_ID smallint(6) NOT NULL, DES_TEX_ID int(11) NOT NULL, PRIMARY KEY (DES_ID,DES_LNG_ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
51
52CREATE TABLE IF NOT EXISTS des_texts ( TEX_ID int(11) NOT NULL, TEX_TEXT text, PRIMARY KEY (TEX_ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
53
54CREATE TABLE IF NOT EXISTS suppliers ( SUP_ID smallint(6) NOT NULL, SUP_BRAND varchar(60) DEFAULT NULL, SUP_SUPPLIER_NR smallint(6) DEFAULT NULL, SUP_COU_ID smallint(6) DEFAULT NULL, SUP_IS_HESS smallint(6) DEFAULT NULL, PRIMARY KEY (SUP_ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
55	
56SELECT Articles.art_article_nr, Suppliers.sup_brand
57       Article_Designations.text, Country_Specific_Designations.text,
58       Article_Criteria_Designations.value,
59       Article_Criteria_Designations.text
60FROM Articles
61JOIN (SELECT Designations.des_id, Des_Texts.tex_text as text
62      FROM Designations
63      JOIN Des_Texts
64      ON Des_Texts.tex_id = Designations.des_tex_id
65      AND Designations.des_lng_id = 19) as Article_Designations
66ON Article_Designations.des_id = Articles.art_complete_des_id
67
68JOIN Suppliers
69ON Suppliers.sup_id = Articles.art_sup_id
70
71JOIN Art_Country_Specific
72ON Art_Country_Specific.acs_art_id = Articles.art_id
73JOIN (SELECT Designations.des_id, Des_Texts.tex_text as text
74      FROM Designations
75      JOIN Des_Texts
76      ON Des_Texts.tex_id = Designations.des_tex_id
77      AND Designations.des_lng_id = 19) as Country_Specific_Designations
78ON Country_Specific_Designations.des_id = Art_Country_Specific.acs_kv_status_des_id
79
80LEFT JOIN (SELECT Article_Criteria.acr_art_id,
81           COALESCE(Art_Cri_Designations.text, Article_Criteria.acr_value) as value,
82           Criteria_Designations.text
83           FROM Article_Criteria
84           LEFT JOIN (SELECT Designations.des_id, Des_Texts.tex_text as text
85                      FROM Designations
86                      JOIN Des_Texts
87                      ON Des_Texts.tex_id = Designations.des_tex_id
88                      AND Designations.des_lng_id = 19) as Art_Cri_Designations
89           ON Art_Cri_Designations.des_id = Article_Criteria.acr_kv_des_id
90           LEFT JOIN (SELECT Criteria.cri_id, Des_Texts.tex_text as text
91                      FROM Criteria
92                      JOIN Designations
93                      ON Designations.des_id = Criteria.cri_des_id
94                      JOIN Des_Texts
95                      ON Des_Texts.tex_id = Designations.des_tex_id
96                      AND Designations.des_lng_id = 19) as Criteria_Designations
97           ON Criteria_Designations.cri_id = Article_Criteria.acr_cri_id
98           ) as Article_Criteria_Designations
99ON Article_Criteria_Designations.acr_art_id = Articles.art_id
100
101WHERE Articles.art_id IN (52277)