CODEWITHSUNDEEP
mysql
merlin
merlin

Reputation: 2917

How to use LEFT JOIN with WHERE on three tables?

In MySQL 5.7.32 I want to retrieve all products from a product table with prices for those products from multiple shops if they are available, if they are not available NULL should be returned for the missing price at that shop.

There are three tables: products, prices, shops

Tables:

-- table shops
id  | name
1   | amazon
2   | ebay
3   | craigslist

-- table products
ARTICLE_ID  | name
123         | article 1
124         | article 2
125         | article 3

-- table prices
DATE    | SHOP_ID       | ARTICLE_ID    |  PRICE
201220  | 1             | 123           | 12.99
201220  | 2             | 123           | 9.99
201220  | 1             | 124           | 10.80
201221  | 1             | 123           | 13.99

-- Desired result
SHOP        | PRODUCT | PRICE
amazon      | 123     | 12.99
amazon      | 124     | 10.80
amazon      | 125     | NULL
ebay        | 123     | 9.99
ebay        | 124     | NULL
ebay        | 125     | NULL

SQL:

SELECT
    s.name,
    p.id,
    mps.price
FROM
    shops s,
    products p
    LEFT JOIN prices mps ON p.ARTICLE_ID = mps.ARTICLE_ID AND mps.DATE = 20201220
WHERE
    s.ID IN ( '1' , '2')

I could not figure out how to show one line per product and shop. Is this even possible with that table setup or do I need to change my database structure?

Upvotes: 1

Views: 37

Answers (1)

forpas
forpas

Reputation: 164099

In the ON clause you must also define the link between shops and prices:

SELECT
    s.name,
    p.ARTICLE_ID,
    mps.price
FROM
    shops s CROSS JOIN products p
    LEFT JOIN prices mps 
    ON s.id = mps.shop_id AND p.ARTICLE_ID = mps.ARTICLE_ID  AND mps.DATE = 201220
WHERE
    s.ID IN ( '1' , '2')
ORDER BY s.name, p.ARTICLE_ID

See the demo.
Results:

> name   | ARTICLE_ID | price
> :----- | ---------: | ----:
> amazon |        123 | 12.99
> amazon |        124 |  10.8
> amazon |        125 |  null
> ebay   |        123 |  9.99
> ebay   |        124 |  null
> ebay   |        125 |  null

Upvotes: 2

Related Questions