Query to extract data based on one column value

Question

There are 2 tables that are supposed to have unique product codes but I came across a case where the product code ended up the same for two of the records in another table. I would like to get the product information based on the latest product year. Let's assume I have the following tables.

Table 1:

recall	id	product_year	product_code
yes	200	2019	3222333
no	201	2020	3222333
yes	202	2021	4332233
no	203	2021	5446553
yes	204	2018	6556677

Table 2:

recall	id	product_year	product_code
no	100	2019	2245643
yes	101	2020	1234543
no	102	2017	4332233
yes	103	2022	5446553
yes	104	2018	3344566

Table 3 contains only unique product code information and other updated information based on the latest product year. For eg: product code 3222333 has only one entry even though Table 1 has 2 entries for 3222333. But the problem comes for codes 4332233 and 5446553 which are present in both Table 1 and Table 2.

Table 3:

country	id	product_code
Brazil	301	3222333
Indonesia	302	4332233
Argentina	303	6556677
Chile	304	2245643
Brazil	305	1234543
Chile	306	5446553
USA	307	3344566

It is known that Table 3 doesn't contain correct data. But I would like to generate a query in mysql to get all the product codes from Table 3 and get the product code related information based on the latest product year. Earlier I was using something like COALESCE(table1.recall, table2.recall, null) assuming that only one value will be present and it works. But for the codes 4332233 and 5446553, it will always pick table 1 column value as recall values for the same product codes are present in both tables 1 and 2. How should I deal with this problem so that I get the data only based on the latest product year?

Expected output:

country	product_code	recall
Brazil	3222333	no
Indonesia	4332233	yes
Argentina	6556677	yes
Chile	2245643	no
Brazil	1234543	yes
Chile	5446553	yes
USA	3344566	yes

Is it possible for the same product_code to have the same product_year in both table1 and table2 ? — blabla_bingo, Jul 29 '22 at 02:58
@blabla_bingo That's an excellent question. Yes it's possible. In that case may be I need to look at the last updated timestamp I believe. — user3376592, Jul 29 '22 at 03:16
Well, if the timestamp data type is used, the timestamp column will be automatically updated to the sysdate when an update is performed for the updated rows, which makes the referential time unreliable I suppose. Personally I would use a product_date instead of product_year and use `year()` function to extract the year if needs be. — blabla_bingo, Jul 29 '22 at 03:31
Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Jul 29 '22 at 09:56

blabla_bingo · Answer 1 · 2022-07-29T08:29:53.993

It did take me some time to realise how convenient it is to use UNION in this case. Below is the code written and tested in workbench.Please note this is currently the best answer I can come up with based on the limited table structure and sample data you provided. Since you say that it's possible for the same product_code to have the same product_year in both table1 and table2 and you have other means to determine the latest one, please tweak the code as you see fit. Hope this helps solve your issue.

select country, recall,ta.product_code as product_code 
from
    (select product_code,product_year,recall 
    from table1 t1
    union
    select product_code,product_year,recall  
    from table2 t2
    ) ta
join
    (select product_code p_c,max(product_year) mx_y 
    from
        (select product_code,product_year 
        from table1 t1
        union
        select product_code,product_year 
        from table2 t2
        ) t_union
    group by product_code
    ) tb
on ta.product_code=p_c and ta.product_year=mx_y
join table3 t3
on p_c=t3.product_code
;

Query to extract data based on one column value

1 Answers1