SQL - Select Statement With Conditions Of Different Table Like Price List Table

CREATE TABLE Price_Table_2020
(
 product Nvarchar(50),
 price INTEGER,
 product_description Nvarchar(12)
);
 
INSERT INTO Price_Table_2020
  (product, price, product_description)
VALUES
  ('ball', '100', 'plastic ball'),
  ('ink', '80', 'ink for pen'),
  ('pen', '1000', 'pen'),
  ('bucket', '200', 'bucket');
 
CREATE TABLE Price_Table_2019
(
  product VARCHAR(6),
  price INTEGER,
  product_description VARCHAR(12)
);
 
INSERT INTO Price_Table_2019
  (product, price, product_description)
VALUES
  ('ball', '90', 'plastic ball'),
  ('ink', '70', 'ink for pen'),
  ('pen', '900', 'pen'),
  ('bucket', '100', 'bucket');
 
CREATE TABLE Price_Table_2018
(
  product VARCHAR(6),
  price INTEGER,
  product_description VARCHAR(12)
);
 
INSERT INTO Price_Table_2018
  (product, price, product_description)
VALUES
  ('ball', '80', 'plastic ball'),
  ('ink', '60', 'ink for pen'),
  ('pen', '800', 'pen'),
  ('bucket', '300', 'bucket');
 
CREATE TABLE Product
(
  product VARCHAR(4),
  invoice_year INTEGER
);
 
INSERT INTO Product
  (product, invoice_year)
VALUES
  ('pen', '2019'),
  ('ball', '2020'),
  ('ink', '2020');
 
 
SELECT pr.product,
       pr.invoice_year,
       pc.price
FROM product pr
JOIN (
  SELECT *, 2020 AS year
  FROM price_table_2020
  UNION ALL
  SELECT *, 2019
  FROM price_table_2019
  UNION ALL
  SELECT *, 2018
  FROM price_table_2018
) pc ON pc.product = pr.product
    AND pc.year = pr.invoice_year
ORDER BY pr.invoice_year, pr.product
 

--Alternately

SELECT p.*, coalesce(p2018.price, p2019.price, p2020.price) AS invoice_price
FROM Product p
LEFT JOIN price_table_2018 p2018 ON p2018.product = p.product and p.invoice_year = 2018
LEFT JOIN price_table_2019 p2019 ON p2019.product = p.product and p.invoice_year = 2019
LEFT JOIN price_table_2020 p2020 ON p2020.product = p.product and p.invoice_year = 2020
ORDER BY p.product;
 
SELECT p.*, p2018.price, p2019.price, p2020.price
FROM Product p
LEFT JOIN price_table_2018 p2018 ON p2018.product = p.product and p.invoice_year = 2018
LEFT JOIN price_table_2019 p2019 ON p2019.product = p.product and p.invoice_year = 2019
LEFT JOIN price_table_2020 p2020 ON p2020.product = p.product and p.invoice_year = 2020
ORDER BY p.product;
 
Select * FROM Product
Select * from Price_Table_2020
Select * from Price_Table_2019
Select * from Price_Table_2018

 
  --Or
  --Restructuring Your Tables to put all the price data in one table, adding a year column to that table to indicate which year the price is applicable to.
 
SELECT pr.product,
       pr.invoice_year,
       pc.price
FROM product pr
JOIN price_table pc ON pc.product = pr.product
                   AND pc.year = pr.invoice_year
ORDER BY pr.invoice_year, pr.product