I'm trying to get a somewhat complicated query working and am not having any luck whatsoever.

Suppose I have the following tables:

cart_items:

+--------------------------------------------+
| item_id  | cart_id | movie_name | quantity |
+--------------------------------------------+
|     0    |    0    | braveheart |    4     |
|     1    |    0    | braveheart |    9     |
|     .    |    .    |      .     |    .     |
|     .    |    .    |      .     |    .     |
|     .    |    .    |      .     |    .     |
|     .    |    .    |      .     |    .     |
+--------------------------------------------+

movies:

+------------------------------+
| movie_id  | movie_name | ... |
+------------------------------+
|     0     | braveheart |  .  |
|     .     |     .      |  .  |
|     .     |     .      |  .  |
|     .     |     .      |  .  |
|     .     |     .      |  .  |
+------------------------------+

pricing:

+-----------------------------------------+
| id  | movie_name | quantity | price_per |
+-----------------------------------------+
|  0  | braveheart |    1     |   1.99    |
|  1  | braveheart |    2     |   1.50    |
|  2  | braveheart |    4     |   1.25    |
|  3  | braveheart |    8     |   1.00    |
|  .  |      .     |    .     |     .     |
|  .  |      .     |    .     |     .     |
|  .  |      .     |    .     |     .     |
|  .  |      .     |    .     |     .     |
|  .  |      .     |    .     |     .     |
+-----------------------------------------+

I need to join the data from the tables, but with the added complexity that I need to get appropriate price_per from the pricing table. Only one price should be returned for each cart_item, and that should be the lowest price from the pricing table where the quantity for the cart item is at least the quantity in the pricing table.

So, the query should return for each item in cart_items the following:

+---------------------------------------------+
| item_id | movie_name | quantity | price_per |
+---------------------------------------------+

Example 1:

Variable passed to the query: cart_id = 0. Return:

+---------------------------------------------+
| item_id | movie_name | quantity | price_per |
+---------------------------------------------+
|    0    | braveheart |    4     |   1.25    |
|    1    | braveheart |    9     |   1.00    |
+---------------------------------------------+

Note that this is a minimalist example and that additional data will be pulled from the tables mentioned (particularly the movies table).

How could this query be composed? I have tried using left joins and subqueries, but the difficult part is getting the price and nothing I have tried has worked.

Thanks for your help.

EDIT:

I think this is similar to what I have working with my "real" tables:

SELECT t1.item_id, t2.movie_name, t1.quantity
FROM cart_items t1
LEFT JOIN movies t2 ON t2.movie_name = t1.movie_name
WHERE t1.cart_id = 0

Assuming I wrote that correctly (I quickly tried to "port over" my real query), then the output would currently be:

+---------------------------------+
| item_id | movie_name | quantity |
+---------------------------------+
|    0    | braveheart |    4     |
|    1    | braveheart |    9     |
+---------------------------------+

The trouble I'm having is joining the price at a certain quantity for a movie. I simply cannot figure out how to do it.