Prioritising column in a left join SQL

I am having issues with a join and I was hoping to get some help. I have one table (table A) that is structured like so:

Fruit	SKU
Apple	001
Apple	X01
Apple	B01
Banana	002
Banana	X02
Banana	B01
Orange	003
Orange	B03

For each fruit, there is a SKU that corresponds to the numbering system used in a year. There are 100s of fruits.

There is another table (Table 2) that looks like this:

SKU_2018	SKU_2017	SKU_2016
001	X01	B01
002	X02	X03

There are different SKU formats for each year between 1990 – 2020. There are no overlapping SKUs, all values are unique. However, I want to map all SKUs to one common year (2018):

What I would like is a table (Table 3) that looks like:

Fruit	SKU	SKU_2018
Apple	001	001
Apple	X01	001
Apple	B01	001
Banana	002	002
Banana	X02	002
Banana	B01	002
Orange	003	003
Orange	B03	003

My code looks like:

select a.fruit, a.sku, b.sku2018
from tableA a
left join tableB b on a.sku = coalesce(b.sku_2018, b.sku_2017, b.sku_2016, so and so for each year)

however, these are returning some NULLs even when there are SKUs that match. I cannot figure out why! I hope that is clear enough. Thanks.

Answer

First of all, this is a bad data model. You should store all SKUs in one column and have Fruit as primary key for Table2. You should also have another column year to denote when the SKU was assigned.

Here’s what you can try:

SELECT a.fruit,
       a.sku,
       coalesce(b.sku_2018, c.sku_2017, d.sku_2016) as sku_2018
FROM   tab1 a
       left join tab2 b
              ON a.sku = b.sku_2018
       left join tab2 c
              ON a.sku = c.sku_2017
       left join tab2 d
              ON a.sku = d.sku_2016; 

DB Fiddle