Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

JOINs in MS SQL Server

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

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