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 product_id:
... FROM product_price pp JOIN orders o ON pp.product_id = o.product_id
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.order_date BETWEEN pp.start_date AND pp.end_date
This way we'll bind every order to select a price from the correct period of time.
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.
The duration of the promotional period (the dates of the start and the end of period) is written in the product_price table.
Type:
SELECT o.id, o.product_id, p.price, order_date FROM product_price p JOIN orders o ON o.product_id = p.product_id AND order_date BETWEEN start_date AND end_date
