Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Medical Center database
Non primary-foreign key JOINs
Non-equi JOINs
Non-equi self JOINs
23. Exercise 1
Summary

## 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

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