-1

I have the following tables

Product table:

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

product_released_country1 table:

id product_code released_year
1 3222333 2000
2 3222333 2001
3 3222333 2003
4 5432322 2000
5 5432322 2001
6 5432322 2010

product_released_country2 table:

id product_code released_year
1 4332233 2000
2 4332233 2001
3 4332233 2009
4 1244532 2000
5 1244532 2001
6 1244532 2010

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:

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

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

user3376592
  • 191
  • 1
  • 13

2 Answers2

1

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

ProDec
  • 5,390
  • 1
  • 3
  • 12
  • Thanks for the query works very well. Let's say if I want to get the latest released year instead of filtering by 2010 and yes/no, (eg: for product code 3222333 - year is 2003, for 4332233 - year is 2009 and for 1244532 - year is 2010, is there an easier way to do that? – user3376592 Jan 05 '22 at 20:42
  • @user3376592 remove left join condition `released_year = 2010` and `SELECT GREATEST(MAX(c1.released_year), MAX(c2.released_year))` will give you latest year for each product. – ProDec Jan 06 '22 at 00:28
  • I tried as you suggested but I am getting null values for the year: SELECT p.id, p.product_type, p.product_code, GREATEST(MAX(c1.released_year), MAX(c2.released_year)) FROM product p LEFT JOIN product_released_country1 c1 ON p.product_code = c1.product_code LEFT JOIN product_released_country2 c2 ON p.product_code = c2.product_code GROUP BY p.id, p.product_type, p.product_code – user3376592 Jan 06 '22 at 00:48
  • @user3376592 check this link https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f204353e4e920479fccffdd31743ecb7 – ProDec Jan 06 '22 at 00:55
  • Wow you are a genius. Learned a lot today and thank you very much sir for your time and help. Also, one final question. Let's assume there are also other columns such as released_month, quantities_released, released_day, etc., in product_released_country1 and product_released_country2 tables. How can we get those values based on the latest_year? – user3376592 Jan 06 '22 at 01:05
  • @user3376592 it will be a quite different query then. similar posts at https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column and https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql for your reference. – ProDec Jan 06 '22 at 09:11
1

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

Luca Riccitelli
  • 364
  • 1
  • 4
  • 13
  • Thank you very useful. I will try this out and I am assuming this would be faster than doing a join? Also, if I want to get the latest released year instead of filtering by 2010 and yes/no, (eg: for product code 3222333 - year is 2003, for 4332233 - year is 2009 and for 1244532 - year is 2010, is there an easier way to do that? – user3376592 Jan 05 '22 at 20:44
  • I think that left join are faster, but my query is easier to understand. To get max year you can use the following query `select p.id, p.product_type, p.product_code , ifnull((select max(t.released_year) 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 ),1970) as max_year from product p` I've updated the [example](https://www.db-fiddle.com/f/iKZCQnzBhV9NbfCPFRfizM/1) – Luca Riccitelli Jan 07 '22 at 07:35
  • Thank you very much and appreciate your time. – user3376592 Jan 15 '22 at 03:19