So, I got that to work, thanks all.
Now I need to put this query inside another query. As follows:
SELECT p.product_id, p.pr_label as label,p.pr_slug as slug,p.pr_status as product_status, psd.pr_si_link as link, psd.link_status,
psd.pr_si_des_sh as short_description, psd.pr_si_des_lo as long_description FROM product p, (
SELECT COALESCE(NULLIF(optl.pr_si_link,'')
,dflt.pr_si_link) AS pr_si_link, optl.link_status, optl.pr_si_des_sh,optl.pr_si_des_lo
FROM prsida AS dflt -- default
LEFT OUTER
JOIN prsida AS optl -- optional
ON optl.product_id = dflt.product_id
AND optl.site_id = 2
WHERE dflt.product_id = 1
AND dflt.site_id = 1
) AS psd WHERE p.product_id = 1
The above works, but I want to change the subquery so that the product_id = 1 part. Something like the following (which doesn’t work " Unknown column ‘p.product_id’ in ‘where clause’ "):
SELECT p.product_id, p.pr_label as label,p.pr_slug as slug,p.pr_status as product_status, psd.pr_si_link as link, psd.link_status,
psd.pr_si_des_sh as short_description, psd.pr_si_des_lo as long_description FROM product p,(
SELECT COALESCE(NULLIF(optl.pr_si_link,'')
,dflt.pr_si_link) AS pr_si_link, optl.link_status, optl.pr_si_des_sh,optl.pr_si_des_lo
FROM prsida AS dflt -- default
LEFT OUTER
JOIN prsida AS optl -- optional
ON optl.product_id = dflt.product_id
AND optl.site_id = 2
WHERE dflt.product_id = p.product_id
AND dflt.site_id = 1
) AS psd WHERE p.product_id = 1
The difference being the subquery WHERE clause:
WHERE dflt.product_id = 1
WHERE dflt.product_id = p.product_id
The reason I want to change this is ultimately as I build the query up I want the outer query WHERE clause to be something like WHERE p.product_id IN (some subquery here):
eg:
SELECT p.product_id, p.pr_label as label,p.pr_slug as slug,p.pr_status as product_status, psd.pr_si_link as link, psd.link_status,
psd.pr_si_des_sh as short_description, psd.pr_si_des_lo as long_description FROM product p, (
SELECT COALESCE(NULLIF(optl.pr_si_link,'')
,dflt.pr_si_link) AS pr_si_link, optl.link_status, optl.pr_si_des_sh,optl.pr_si_des_lo
FROM prsida AS dflt -- default
LEFT OUTER
JOIN prsida AS optl -- optional
ON optl.product_id = dflt.product_id
AND optl.site_id = 2
WHERE dflt.product_id = p.product_id
AND dflt.site_id = 1
) AS psd WHERE p.product_id IN (some subquery here)
Thanks