I have the following tables

Product table:

product_released_country1 table:

product_released_country2 table:

The products that gets released in different countries are saved in 2 different tables. I need a query that would return all products by checking both tables to see if any of the products have been released in 2010. If the product was released in 2010, then return 'yes' for the specific product. If the product hasn't been released in 2010 or haven't been released at all in any years (eg: product_code 7543223), then the query should return a 'no' for the is_product_released_in_2010 column.

Expected output:

I tried joining both the tables using union all but still not getting the desired result. Any help would be greatly appreciated. Thank you.

CodePudding user response：

LEFT JOIN and check existence

SELECT p.id, p.product_type, p.product_code, CASE WHEN MAX(c1.id) IS NOT NULL OR MAX(c2.id) IS NOT NULL THEN 'yes' ELSE 'no' END is_product_released_in_2010
FROM product p
LEFT JOIN product_released_country1 c1 ON p.product_code = c1.product_code AND c1.released_year = 2010
LEFT JOIN product_released_country2 c2 ON p.product_code = c2.product_code AND c2.released_year = 2010
GROUP BY p.id, p.product_type, p.product_code

demo

CodePudding user response：

You can do it with a count and an union all

select 
p.id, 
p.product_type, 
p.product_code ,
(select count(*) from
  (select product_code, released_year from product_released_country1
  union all
  select product_code, released_year from product_released_country2) as t
where t.product_code = p.product_code and t.released_year=2010)>0 as is_product_released_in_2010
from product p

example here