SELECT 
  cscart_products_categories.category_id as variant_id, 
  ccd.category as variant 
FROM 
  cscart_products_categories 
  LEFT JOIN cscart_products as products ON products.product_id = cscart_products_categories.product_id 
  INNER JOIN cscart_categories as cc ON cscart_products_categories.category_id = cc.category_id 
  AND cc.company_id = 0 
  AND cc.status = 'A' 
  LEFT JOIN cscart_category_descriptions as ccd ON cscart_products_categories.category_id = ccd.category_id 
  AND ccd.lang_code = 'en' 
  LEFT JOIN cscart_product_features_values as var_val_73 ON var_val_73.product_id = products.product_id 
  AND var_val_73.lang_code = 'en' 
  AND var_val_73.feature_id = 73 
  LEFT JOIN cscart_companies AS companies ON companies.company_id = products.company_id 
  INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id 
  INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id 
  AND (
    cscart_categories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_categories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_categories.usergroup_ids
    )
  ) 
  AND cscart_categories.status IN ('A', 'H') 
  AND cscart_categories.storefront_id IN (0, 1) 
  LEFT JOIN cscart_supplier_links ON cscart_supplier_links.object_id = products.product_id 
  AND cscart_supplier_links.object_type = 'P' 
  LEFT JOIN cscart_master_products_storefront_offers_count AS master_products_storefront_offers_count ON master_products_storefront_offers_count.product_id = products.product_id 
  AND master_products_storefront_offers_count.storefront_id = 1 
  LEFT JOIN cscart_product_sales ON cscart_product_sales.product_id = products.product_id 
  AND cscart_product_sales.category_id = products_categories.category_id 
WHERE 
  1 = 1 
  AND ccd.category_id IN ('2198') 
  AND (
    var_val_73.variant_id IN (35728)
  ) 
  AND (
    companies.status IN ('A') 
    OR products.company_id = 0
  ) 
  AND products.company_id IN (13, 0) 
  AND (
    products.usergroup_ids = '' 
    OR FIND_IN_SET(0, products.usergroup_ids) 
    OR FIND_IN_SET(1, products.usergroup_ids)
  ) 
  AND products.status IN ('A') 
  AND products.master_product_status IN ('A') 
  AND products.master_product_id = 0 
  AND (
    products.company_id > 0 
    OR master_products_storefront_offers_count.count > 0
  ) 
  AND (
    1 
    AND products.product_id IN (
      9767, 11145, 12518, 12565, 20277, 23359, 
      30483, 33741, 36311, 36312, 36428, 
      36429, 36430, 36431, 24749, 24750, 
      24751, 24753, 24754, 24755, 24756, 
      24757, 24758, 24759, 35648, 35649, 
      35650, 35651, 35652, 35653, 35654, 
      35655, 35656, 35657, 35661, 35662, 
      35663, 35664, 35665, 35666, 35669, 
      35670, 35671, 35672, 35673, 35674, 
      35678, 35679, 35680, 35681, 35682, 
      35683, 9108, 9109, 9110, 9111, 9171, 
      9177, 9816, 12494, 12539, 15838, 15839, 
      15840, 15841, 15842, 15843, 15844, 
      15845, 15847, 15848, 15849, 18487, 
      18489, 21776, 27956, 31413, 35483, 
      35489, 36620, 1010, 8940, 9106, 9139, 
      9154, 9807, 9869, 10624, 11159, 12018, 
      21092, 21775, 24596, 25171, 25252, 
      25253, 25451, 27094, 27095, 31119, 
      31120, 31433, 31458, 31764, 31765, 
      31766, 31767, 31768, 31769, 33403, 
      36330, 36334, 36596, 36600, 36601
    )
  ) 
GROUP BY 
  cscart_products_categories.category_id 
ORDER BY 
  ccd.category ASC