How to pull data from a nested query?

Question

MySQL

Good day! I pull out links to products, their names and articles. Tell me how to get sku from the oc_product table in this query

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` )

Thanks in advance!

Answer 1

You have the oc_product table inside the subquery, so you first need to display it there, and only then "on top":

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: and format the requests normally, it will be more convenient to read.

Answer 2

SELECT name, p.sku, CONCAT(...