SQL Example

SET @LNGID = 4;   
 
   SET @ART_ID = 29154; 
   
   SELECT DISTINCT * 
        
   FROM (
   (SELECT
          ARTICLES.ART_ID, 
          ARTICLES.ART_ARTICLE_NR,
          ARTICLES.ART_SUP_BRAND,
          CONCAT_WS(' ', get_text(ARTICLES.ART_COMPLETE_DES_ID, @LNGID), get_text(ARTICLES.ART_DES_ID, @LNGID) ) COLLATE utf8mb4_general_ci AS ART_PRODUCT_NAME
          
    FROM 
          ART_LOOKUP
          INNER JOIN MANUFACTURERS ON MANUFACTURERS.MFA_ID = ART_LOOKUP.ARL_BRA_ID
          INNER JOIN ART_LOOKUP AS ART_LOOKUP_CROSS ON ART_LOOKUP_CROSS.ARL_SEARCH_NUMBER = ART_LOOKUP.ARL_SEARCH_NUMBER
                 AND ART_LOOKUP_CROSS.ARL_KIND = 1
                 AND ART_LOOKUP_CROSS.ARL_BRA_ID = MANUFACTURERS.MFA_SUP_ID
          
          INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP_CROSS.ARL_ART_ID
    WHERE 
          ART_LOOKUP.ARL_ART_ID = @ART_ID AND ART_LOOKUP.ARL_KIND = 4)
          
 UNION ALL 
   
    (SELECT
          ARTICLES.ART_ID, 
          ARTICLES.ART_ARTICLE_NR,
          ARTICLES.ART_SUP_BRAND,
          CONCAT_WS(' ', get_text(ARTICLES.ART_COMPLETE_DES_ID, @LNGID), get_text(ARTICLES.ART_DES_ID, @LNGID) ) AS ART_PRODUCT_NAME
          
    FROM 
          ART_LOOKUP 
          INNER JOIN MANUFACTURERS ON MANUFACTURERS.MFA_SUP_ID = ART_LOOKUP.ARL_BRA_ID
          INNER JOIN ART_LOOKUP AS ART_LOOKUP_CROSS ON ART_LOOKUP_CROSS.ARL_SEARCH_NUMBER = ART_LOOKUP.ARL_SEARCH_NUMBER
                 AND ART_LOOKUP_CROSS.ARL_KIND = 4
                 AND ART_LOOKUP_CROSS.ARL_BRA_ID =  MANUFACTURERS.MFA_ID
          
          INNER JOIN ARTICLES ON ARTICLES.ART_ID = ART_LOOKUP_CROSS.ARL_ART_ID
    WHERE 
          ART_LOOKUP.ARL_ART_ID = @ART_ID AND ART_LOOKUP.ARL_KIND = 1) 
          
  UNION ALL 
  
    (SELECT
          NULL, 
          ART_LOOKUP.ARL_DISPLAY_NR,
          ART_LOOKUP.ARL_BRA_BRAND,
          CONCAT_WS(' ', get_text(ARTICLES.ART_COMPLETE_DES_ID, @LNGID), get_text(ARTICLES.ART_DES_ID, @LNGID) ) AS ART_PRODUCT_NAME
          
    FROM 
          ART_LOOKUP 
          INNER JOIN ARTICLES ON ARTICLES.ART_ID = @ART_ID
    WHERE 
          ART_LOOKUP.ARL_ART_ID = @ART_ID AND ART_LOOKUP.ARL_KIND = 3)
          
  ) as CROSS_REFF