I have created 3 sample tables which I want to link together, as well as a URL to the case in DB Fiddle with mySQL 8.0.
https://www.db-fiddle.com/f/6mmFrw3JTWkVVznaH5juNp/0
The goal is that I get the prices for the stores "Amazon" and "Ebay" for all items from table "products_info". As soon as no prices are available in my "merchants_products" table, the SKU for the store should still be displayed. So at the end not 3 rows, but 4 rows should be displayed.
Table 1: merchants contains all locations where my items are being sold.
Table2: merchants_products contains all the items plus prices that I sell in the different locations.
Table3: products_info contains all the items I want to get my information about.
create table merchants_products
(
SKU char(10) null,
MERCHANT_ID int null,
PRICE int null
);
INSERT INTO merchants_products (SKU, MERCHANT_ID, PRICE) VALUES ('13749461', 1, 3);
INSERT INTO merchants_products (SKU, MERCHANT_ID, PRICE) VALUES ('13753824', 1, 5);
INSERT INTO merchants_products (SKU, MERCHANT_ID, PRICE) VALUES ('13749461', 2, null);
INSERT INTO merchants_products (SKU, MERCHANT_ID, PRICE) VALUES ('13753770', 2, 4);
INSERT INTO merchants_products (SKU, MERCHANT_ID, PRICE) VALUES ('13749461', 3, 3);
INSERT INTO merchants_products (SKU, MERCHANT_ID, PRICE) VALUES ('13753770', 3, 4);
INSERT INTO merchants_products (SKU, MERCHANT_ID, PRICE) VALUES ('13753824', 3, 5);
create table merchants
(
ID int null,
NAME varchar(255) null
);
INSERT INTO merchants (ID, NAME) VALUES (1, 'amazon');
INSERT INTO merchants (ID, NAME) VALUES (2, 'ebay');
INSERT INTO merchants (ID, NAME) VALUES (3, 'tesco');
create table product_info
(
SKU char(10) null,
NAME varchar(255) null
);
INSERT INTO product_info (SKU, NAME) VALUES ('13749461', 'Artikel1');
INSERT INTO product_info (SKU, NAME) VALUES ('13753770', 'Artikel2');
SELECT
pi.SKU,
mp.PRICE,
mc.NAME
FROM product_info pi
LEFT JOIN merchants_products mp
ON mp.SKU= pi.SKU and mp.MERCHANT_ID IN (1,2)
LEFT JOIN merchants mc
ON mc.ID = mp.MERCHANT_ID