What worked? Lucas option doesn't seem like a great option.
You could use something like this for the HAVING
SELECT product, region, status
FROM products prd
INNER JOIN pd_status S on s.product_status_key=prd.status_key
WHERE prd.product_status in ('active', 'on hold')
AND s.sa_status in ('canceled', 'On Hold')
GROUP BY product, region, status
However, I'm not sure it can work. You didn't post your DDL so we don't know if region is a column from Product or Pd_Status. It could have helped if you use complete names of the fields like you did on the where clauses.
For even better help, you could have posted sample data. That way we can have a better idea of what's needed. You might even need a LEFT JOIN or no JOIN at all, but is hard for us to know if we don't see what you see.
Luis C.General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2