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 ProductName), it's price and the producer's name (rename the column to CompanyName). You should display all products that haven't been sold in this interval, also the ones that don't belong to any company.
Stuck? Here's a hint!
That's a tough one!
First you have to LEFT JOIN the SalesHistory 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. We'd like to display all products, even the ones that belong to no company, so we need to use LEFT JOIN.
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.ProductId IS NULL
Type:
SELECT P.Name AS ProductName, P.Price, Prod.Name AS CompanyName FROM Product P LEFT JOIN SalesHistory Sh ON P.Id = Sh.ProductId AND Date BETWEEN '2015-02-01' AND '2015-02-05' LEFT JOIN Producer Prod ON P.ProducerId = Prod.Id WHERE Sh.ProductId IS NULL
