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