products (
  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID товара',
  price decimal(19, 2) DEFAULT NULL COMMENT 'Розничная цена',
)

products_lang (

  product_id int(11) UNSIGNED DEFAULT NULL,
  lang char(3) DEFAULT NULL,
  name varchar(160) DEFAULT NULL,

  CONSTRAINT FK_products_lang_product_id FOREIGN KEY (product_id)
  REFERENCES products (id) ON DELETE CASCADE ON UPDATE CASCADE
)

SELECT
  products.id,
  products_lang.name,
  products_lang.lang
FROM products
  LEFT OUTER JOIN products_lang
    ON products.id = products_lang.product_id
    AND products_lang.lang = 'uk'
    GROUP BY products.id

SELECT
    products.*,
    currency.name AS currency_tag,
    products_types.name AS product_type_name,
    products_type_stock.name_lang AS product_type_stock_name,
    products_types.name_lang AS product_type_name_lang,
    products_type_units.name_lang AS product_type_unit_lang,
    products_type_sale.name_lang AS product_type_sale_lang,
    products_status.name AS product_status_name,
    products.is_options,
    products.discount_id,
    products.discount_options,
    products.on_showcase,
    products.vk_sync,
    products.price_old_percent,
    products.price_old_options,
    products.country_id,
    products.hit_sale,
    discounts.products_all,
    discounts.name,
    discounts.discount,
    discounts.discount_type,
    discounts.discount_start,
    discounts.discount_end,
    products.warehouse_id,
    products.warehouse_options,
    adjacency_lists.category_name,
    currency.currency_name,
    categories.category_main_name,
    shops.shop_name,
    users.email,
    users.first_name,
    users.last_name,
    users.avatar,
    users.avatar_type,
    users.phone
FROM (SELECT
    products.id,
    products.is_options,
    products.discount_id,
    products.discount_options,
    products.price_old_percent,
    products.price_old_options,
    products.country_id,
    products.hit_sale,
    products.on_showcase,
    products.vk_sync,
    products.currency_id,
    products.category_id,
    products.category_main_id,
    products.shop_id,
    products.user_id,
    products.type_id,
    products.stock_id,
    products.unit_id,
    products.type_sale_id,
    products.note_currency_id,
    products.status_id,
    products.warehouse_id,
    products.warehouse_options,
    products.slug,
    products.price,
    products.price_wholesale,
    products.stock_num,
    products.sku,
    products.video_link,
    products.trash,
    products.deleted,
    products.ban,
    products.approve,
    products.reject,
    products.sales,
    products.views,
    products.date_start,
    products.date_end,
    products.moneyback,
    products.note_price_buy,
    products.note_url_site,
    products.note_url_product,
    products.note_descr,
    products.term_prepend,
    products.pre_order,
    products.freebie,
    products.created,
    products.updated,
    products.order_by,
    products_lang.name AS product_name,

(SELECT GROUP_CONCAT(DISTINCT name) AS products_list FROM products_lang WHERE products_lang.product_id=products.id) AS product_names,
    (SELECT GROUP_CONCAT(DISTINCT name) AS categories_list FROM categories_lang WHERE categories_lang.category_id=products.category_main_id) AS categories_names,
	  (SELECT GROUP_CONCAT(DISTINCT name) AS shops_list FROM shops_lang WHERE shops_lang.shop_id = '9') AS shops_names
  
  FROM products
    LEFT OUTER JOIN products_lang
      ON products_lang.product_id = products.id
      AND products_lang.lang = IF(products_lang.lang = 'ru','ru',NULL)
      
  ) products

  LEFT OUTER JOIN (SELECT
      currency.id,
      currency.name,
      currency_lang.name AS currency_name
    FROM currency
      LEFT JOIN currency_lang
        ON currency.id = currency_lang.currency_id
	    	AND currency_lang.lang = IF(currency_lang.lang = 'ru','ru',NULL)) currency
    ON products.currency_id = currency.id
  LEFT OUTER JOIN (SELECT
      adjacency_lists.id,
      adjacency_lists_lang.name AS category_name
    FROM adjacency_lists
      LEFT JOIN adjacency_lists_lang
        ON adjacency_lists.id = adjacency_lists_lang.al_id
		  AND adjacency_lists_lang.lang = IF(adjacency_lists_lang.lang = 'ru','ru',NULL)) adjacency_lists
    ON products.category_id = adjacency_lists.id
  LEFT OUTER JOIN (SELECT
      categories.id,
      categories_lang.name AS category_main_name
    FROM categories
      LEFT JOIN categories_lang
        ON categories.id = categories_lang.category_id
		AND categories_lang.lang = IF(categories_lang.lang = 'ru','ru',NULL)) categories
    ON products.category_main_id = categories.id
  LEFT OUTER JOIN (SELECT
      shops.id,
      shops_lang.name AS shop_name
    FROM shops
      LEFT JOIN shops_lang
        ON shops.id = shops_lang.shop_id
		AND shops_lang.lang = IF(shops_lang.lang = 'ru','ru',NULL)) shops
    ON products.shop_id = shops.id
  LEFT OUTER JOIN users
    ON products.user_id = users.id
  LEFT OUTER JOIN products_types
    ON products.type_id = products_types.id
  LEFT OUTER JOIN products_type_stock
    ON products.stock_id = products_type_stock.id
  LEFT OUTER JOIN products_type_units
    ON products.unit_id = products_type_units.id
  LEFT OUTER JOIN products_type_sale
    ON products.type_sale_id = products_type_sale.id
  LEFT OUTER JOIN products_status
    ON products.status_id = products_status.id
  LEFT OUTER JOIN discounts
    ON products.discount_id = discounts.id

WHERE products.shop_id = '9' 
  AND products.id = 2064
  GROUP BY products.id

(SELECT GROUP_CONCAT(DISTINCT name) AS products_list FROM products_lang WHERE products_lang.product_id=products.id) AS product_names

FROM products
    LEFT OUTER JOIN products_lang
      ON products_lang.product_id = products.id
      AND products_lang.lang = IF(products_lang.lang = 'ru','ru',NULL)

$p_names = explode(",", $result['product_names'];
$product_name = ($result['product_name']) ? $result['product_name'] : $p_names[0]);