Medical Center database
Non primary-foreign key JOINs
Non-equi JOINs
Non-equi self JOINs
Instruction
Good! These two tables allow us to introduce a useful example.
Suppose you want to check the price of a given product over a period when the product is on special offer. First, we would have to join the two tables with ProductId:
... FROM ProductPrice Pp JOIN Orders O ON Pp.ProductId = O.ProductId
But if we do this, we won't be able to determine the price of a given product. To do that, we have to add one more condition:
AND O.OrderDate BETWEEN Pp.StartDate AND Pp.EndDate
This way we'll bind every order to select a price from the correct period of time.
Exercise
Now it's your turn!
For the products bought with a special price, show its ID, its price, the ID of every order and the date when the order was placed.
Stuck? Here's a hint!
The duration of the promotional period (the dates of the start and the end of period) is written in the ProductPrice table.
Type:
SELECT O.Id, O.ProductId, P.Price, OrderDate FROM ProductPrice P JOIN Orders O ON O.ProductId = P.ProductId AND OrderDate BETWEEN StartDate AND EndDate
