Healthy Shop database
MULTIPLE JOINs – Exercises
11. Exercise 7

Instruction

Amazing! How about something a bit trickier?

Exercise

For products that weren't sold even once between 2015-02-01 and 2015-02-05, show the product name (rename the column to product_name), it's price and the producer's name (rename the column to company_name).

Stuck? Here's a hint!

That's a tough one!

First you have to LEFT JOIN the sales_history to the product table.

Mind that we are interested ONLY in products sold BETWEEN the 2015-02-01 AND 2015-02-05 so we need to add filtering before the LEFT JOIN as applied:

AND date BETWEEN '2015-02-01' AND '2015-02-05'

Then we can safely join the producer table.

However, mind that we wanted to return the name of products that weren't sold on that day, so we have to apply one final condition:

WHERE sh.product_id IS NULL

Type:

SELECT
  p.name AS product_name,
  p.price,
  prod.name AS company_name
FROM product p
LEFT JOIN sales_history sh
  ON p.id = sh.product_id
  AND date BETWEEN '2015-02-01' AND '2015-02-05'
JOIN producer prod
  ON p.producer_id = prod.id
WHERE sh.product_id IS NULL

Console

Code editor

Result

TableConsole