SELECT DISTINCT
  PARTTYP, PARTNUM, 
  GROUP_CONCAT( DISTINCT
    CONCAT_WS('/', GROUPNUM, TRIM(alltext_sa_parts_v.SANUM), STRKVER, alltext_sa_parts_v.SUBGRP) 
    SEPARATOR ';') GRP_SA_STRK_SUBG, 
  SUBMOD,
  alltext_bm_saidx_v.CATNUM,
  CLASS, AGGTYPE, TYPE, SUBBM1, SUBBM2, APPINF, SALESDES, TRIM(REMARKS) REMARKS,
  IFNULL(noun_L.NOUN, noun_E.NOUN) AS PART_NOUN,
  TRIM(IFNULL(des_L.DESCRIPTION, des_E.DESCRIPTION)) AS PART_DESCRIPTION
FROM 
  alltext_sa_parts_v
  #связь SA
  JOIN alltext_sa_strokes_v 
    ON alltext_sa_strokes_v.SANUM = alltext_sa_parts_v.SANUM
    AND LOCATE(CONCAT(alltext_sa_strokes_v.STRKVER,','), alltext_sa_parts_v.STROKE) > 0 # соместимость STROKE
  #связь группа
  JOIN alltext_bm_saidx_v 
    ON alltext_sa_parts_v.SANUM = alltext_bm_saidx_v.SANUM
  # связь авто
  JOIN alltext_models_v
    ON alltext_models_v.CATNUM = alltext_bm_saidx_v.CATNUM
    AND alltext_models_v.TYPE = alltext_sa_strokes_v.MODEL
    # точно уточнить SUBM
    AND (
      alltext_sa_strokes_v.SUBMOD LIKE '%BIS%' # период SUBM проверить в php
      OR (
        # точно уточнить SUBM1
        MOD(LOCATE(alltext_models_v.SUBBM1, alltext_sa_strokes_v.SUBMOD), 3) = 1
        # точно уточнить SUBM2, MOD(6) - нельзя
        AND (alltext_models_v.SUBBM2 = '' 
          OR MOD(LOCATE(CONCAT(alltext_models_v.SUBBM1,alltext_models_v.SUBBM2), alltext_sa_strokes_v.SUBMOD), 3) = 1)
      )
    )
  # PART NOUN
  LEFT OUTER JOIN alltext_part_nouns_v noun_L
    ON noun_L.NOUNIDX = alltext_sa_parts_v.NOUNIDX AND noun_L.LANG = 'R'
  LEFT OUTER JOIN alltext_part_nouns_v noun_E
    ON noun_E.NOUNIDX = alltext_sa_parts_v.NOUNIDX AND noun_E.LANG = 'E'
  # PART DESCRIPTION
  LEFT OUTER JOIN alltext_part_descs_v des_L
    ON des_L.DESCIDX = alltext_sa_parts_v.DESCIDX AND des_L.LANG = 'R'
  LEFT OUTER JOIN alltext_part_descs_v des_E
    ON des_E.DESCIDX = alltext_sa_parts_v.DESCIDX AND des_E.LANG = 'E'
WHERE 
  SEARCH_NUMBER LIKE 'A0015458628%'
GROUP BY 
  PARTTYP, PARTNUM, CLASS, AGGTYPE, TYPE, SUBBM1, SUBBM2, APPINF, alltext_models_v.CATNUM, SUBMOD
LIMIT 50