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

Console

Code editor

Result

TableConsole