Как вытащить данные при вложенном запросе?

Question

[Сергей Ерин]

Доброго времени суток! Вытаскиваю ссылки на товары, их названия и артикулы. Подскажите, как вытащить в этом запросе sku из таблицы oc_product

SELECT name, CONCAT('https://домен/', IF(ua2.keyword IS NULL,'',CONCAT(ua2.keyword, '/')), IF(ua3.keyword IS NULL,'',CONCAT(ua3.keyword, '/')), 
IF(ua4.keyword IS NULL,'',CONCAT(ua4.keyword, '/')), ua1.keyword) AS url
FROM (SELECT CONCAT( 'product_id=', p.product_id ) AS product_query, name, CONCAT( 'category_id=', pc.category_id ) AS category_query,
CONCAT( 'series_id=', ps.series_id ) AS series_query, CONCAT( 'subcategory_id=', psc.subcategory_id ) AS subcategory_query
FROM `oc_product_description` pd
LEFT JOIN oc_product p ON (p.product_id=pd.product_id)
LEFT JOIN oc_product_to_category pc ON (pc.product_id=p.product_id)
LEFT JOIN oc_product_to_series ps ON (ps.product_id=p.product_id)
LEFT JOIN oc_product_to_subcategory psc ON (psc.product_id=p.product_id)
WHERE p.date_available <= NOW() AND p.status = '1')pd
LEFT JOIN oc_url_alias ua1 ON ( pd.`product_query` = ua1.`query` )
LEFT JOIN oc_url_alias ua2 ON ( pd.`category_query` = ua2.`query` )
LEFT JOIN oc_url_alias ua3 ON ( pd.`series_query` = ua3.`query` )
LEFT JOIN oc_url_alias ua4 ON ( pd.`subcategory_query` = ua4.`query` )

Заранее спасибо!

Answer 1

[Сергей c0re]

У вас таблица oc_product внутри подзапроса, поэтому сначала нужно вывести там, а уже потом "сверху":

SELECT
    name,
    sku,
    CONCAT('https://домен/', IF(ua2.keyword IS NULL,'',CONCAT(ua2.keyword, '/')), IF(ua3.keyword IS NULL,'',CONCAT(ua3.keyword, '/')), IF(ua4.keyword IS NULL,'',CONCAT(ua4.keyword, '/')), ua1.keyword) AS url
  FROM (
    SELECT
        name,
        p.sku,
        CONCAT( 'product_id=', p.product_id ) AS product_query,
        CONCAT( 'category_id=', pc.category_id ) AS category_query,
        CONCAT( 'series_id=', ps.series_id ) AS series_query,
        CONCAT( 'subcategory_id=', psc.subcategory_id ) AS subcategory_query
      FROM `oc_product_description` pd
      LEFT JOIN oc_product p ON (p.product_id=pd.product_id)
      LEFT JOIN oc_product_to_category pc ON (pc.product_id=p.product_id)
      LEFT JOIN oc_product_to_series ps ON (ps.product_id=p.product_id)
      LEFT JOIN oc_product_to_subcategory psc ON (psc.product_id=p.product_id)
      WHERE p.date_available <= NOW()
        AND p.status = '1'
  ) pd
  LEFT JOIN oc_url_alias ua1 ON ( pd.`product_query` = ua1.`query` )
  LEFT JOIN oc_url_alias ua2 ON ( pd.`category_query` = ua2.`query` )
  LEFT JOIN oc_url_alias ua3 ON ( pd.`series_query` = ua3.`query` )
  LEFT JOIN oc_url_alias ua4 ON ( pd.`subcategory_query` = ua4.`query` )
;

PS: и форматируйте запросы нормально, читать будет удобнее.