Complex (?) SQL Query

Databases
1

I am wanting to pull a list of all product attributes that belong to a particular product (product_id = 15) from one table, and from another product pull each attributes description.
If the description is empty for that product AND site (in the case site_id = 2) relation (every site has potential for a unique description) then I want to pull the default description (site_id = 1).

This is what I have so far, and it isn’t working properly.
SELECT
pa.product_id
, pa.pr_ty_at_g_la AS group_label
, pa.pr_ty_at_g_sor
, pa.pr_ty_att_label AS attribute_label
, pa.pr_att_value AS attribute_value
, pasd.pr_att_des AS attribute_description

FROM
pratt AS pa

INNER JOIN
( SELECT COALESCE(NULLIF(optl.pr_att_des,‘’),NULLIF(dflt.pr_att_des,‘’)) AS pr_att_des FROM prattsid AS dflt
LEFT OUTER
JOIN prattsid AS optl
ON optl.pr_att_id = dflt.pr_att_id
AND optl.site_id = 2
WHERE dflt.site_id = 1 ) AS pasd
WHERE pa.product_id = 15 AND pa.pr_ty_at_g_la=‘Other’

Thanks

2

Example data from pratt:

pr_att_id	product_id	pr_ty_att_id	pr_ty_at_g_sor	pr_att_value	pr_ty_att_label	pr_ty_at_g_la
3	        15	          3	           0	         19.24	       Interest Rate	Other

Example data for prattsid

pr_att_id	site_id	pr_att_des
3	       1          This is default description for Interest Rate
3	       2          This is site 2's description for Interest Rate

So with the above descriptions it would pick “This is site 2’s description for Interest Rate” as the description.

However with the following data it would pick “This is default description for Interest Rate” as the description for the current site (site_id = 2) is blank(or possibly null):

pr_att_id	site_id	pr_att_des
3	       1          This is default description for Interest Rate
3	       2
3 

SELECT
    pa.product_id
  , pa.pr_ty_at_g_la AS group_label
  , pa.pr_ty_at_g_sor
  , pa.pr_ty_att_label AS attribute_label
  , pa.pr_att_value AS attribute_value
  , COALESCE(optl.pr_att_des, dflt.pr_att_des, '') AS attribute_description
FROM pratt AS pa
LEFT OUTER JOIN prattsid AS optl
ON optl.pr_att_id = pa.pr_att_id
AND optl.site_id = 2
LEFT OUTER JOIN prattsid AS dflt
ON dflt.pr_att_id = pa.pr_att_id
AND dflt.site_id = 1
WHERE pa.product_id = 15 AND pa.pr_ty_at_g_la='Other'
4

Thanks so much, works a treat!