MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.27


CREATE TABLE product (
 id INT PRIMARY KEY,
 product_type VARCHAR(20),
 product_code VARCHAR(20)
);

CREATE TABLE product_released_country1 (
 id INT PRIMARY KEY,
 product_code VARCHAR(20),
 released_year INT
);

CREATE TABLE product_released_country2 (
 id INT PRIMARY KEY,
 product_code VARCHAR(20),
 released_year INT
);

INSERT INTO product VALUES 
(200, 'Camera', '3222333'),
(201, 'Television', '5432322'),
(202, 'PC', '4332233'),
(203, 'Mac', '1244532'),
(204, 'Notebook', '7543223');

INSERT INTO product_released_country1
VALUES
(1, '3222333', 2000),
(2, '3222333', 2001),
(3, '3222333', 2003),
(4, '5432322', 2000),
(5, '5432322', 2001),
(6, '5432322', 2010);

INSERT INTO product_released_country2
VALUES
(1, '4332233', 2000),
(2, '4332233', 2001),
(3, '4332233', 2009),
(4, '1244532', 2000),
(5, '1244532', 2001),
(6, '1244532', 2010);
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


id
product_type
product_code
is_product_released_in_2010


200
Camera
3222333
no

201
Television
5432322
yes

202
PC
4332233
no

203
Mac
1244532
yes

204
Notebook
7543223
no

id	product_type	product_code	is_product_released_in_2010
200	Camera	3222333	no
201	Television	5432322	yes
202	PC	4332233	no
203	Mac	1244532	yes
204	Notebook	7543223	no