SQL query to link 3 tables with all contents

Viewed 35

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
0 Answers
Related