Geek Answers Handbook

How to make left joins with a smallest-per-group request?

I am trying to get a somewhat complicated request and I have no luck.

Suppose I have the following tables:

cart_items:

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

films:

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

prices:

 +-----------------------------------------+ | 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 combine data from tables, but with added complexity, which I need to get price_per from the pricing table. Only one price should be returned for each cart_item , and it should be the lowest price from the pricing table, where quantity for the cart item is at least the quantity in the pricing table.

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

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


Example 1:

The variable passed to the request: cart_id = 0. Return:

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


Please note that this is a minimalist example and that additional data will be extracted from the tables mentioned (in particular, in the movie table).

How can this request be compiled? I tried using left joins and subqueries, but the tricky part is the price, and none of what I tried worked.

Thank you for your help.

EDIT:

I think it looks like I'm 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 this correctly (I quickly tried to "overload" my real request), then the output will be for the time being:

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

The problem I am facing is to join the price in a certain amount for the film. I just can't figure out how to do this.

+4
source share
1 answer
 SELECT t1.item_id, t1.movie_name, t1.quantity, t2.price_per FROM cart_items t1 INNER JOIN ( SELECT c.quantity, c.movie_name, IF(p.price_per IS NOT NULL, MAX(p.price_per), MIN(p2.price_per)) AS `price_per` FROM cart_items c LEFT JOIN pricing p ON p.movie_name = c.movie_name AND p.quantity >= c.quantity LEFT JOIN pricing p2 ON p2.movie_name = c.movie_name AND p2.quantity < c.quantity GROUP BY c.quantity, c.movie_name ) t2 ON t2.movie_name = t1.movie_name AND t2.quantity = t1.quantity

script: http://sqlfiddle.com/#!2/072e8

Sincerely.

+1
source

More articles:


All Articles