MAX() Issues

[tbarsness]

I have three tables defined here:

 

1.  item

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id_item     | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| name        | varchar(50)      | NO   |     |         |                | 
| description | text             | NO   |     |         |                | 
| image_name  | varchar(50)      | NO   |     |         |                | 
| active      | enum('0','1')    | NO   |     | 1       |                | 
+-------------+------------------+------+-----+---------+----------------+

 

2.  price

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id_price | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| id_item  | int(10) unsigned | NO   |     |         |                | 
| min_qty  | int(10) unsigned | NO   |     |         |                | 
| price    | float(7,2)       | NO   |     |         |                | 
+----------+------------------+------+-----+---------+----------------+

 

3.  cart

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id_cart    | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| session_id | char(32)         | NO   |     |         |                | 
| id_item    | int(10) unsigned | NO   |     |         |                | 
| quantity   | int(10) unsigned | NO   |     |         |                | 
+------------+------------------+------+-----+---------+----------------+

 

I'm trying to write a query to join the three together, selecting the minimum `price`.`price` (and thus maximum `price`.`min_qty` that is less than `cart`.`quantity`).  I'm running into some problems. 

 

This query:

 

SELECT *, MAX(`min_qty`) AS `maxquantity` 
FROM `cart`,`item`,`price` 
WHERE `session_id` = 'cbf4d44213e5250ce3e444aff138313d' 
AND `cart`.`id_item` = `item`.`id_item` 
AND `cart`.`id_item` = `price`.`id_item` 
AND `price`.`min_qty` < `cart`.`quantity` 

GROUP BY `cart`.`id_cart`

Returns me everything I hoped it would, including a correct maxquantity.  The problem is the rest of the row is from an incorrect part of the quantity row.  How do I select data from the row that has this max quantity?  I've tried a couple of things with HAVING and haven't had any luck.  Do I have to write an inner query?  How would I go about doing that?

[fenway]

This is the standard group-wise maximum issue... the only meaningful columns are the ones you group by... if you want anything else, you'll have to join the table back to itself using (id_cart, and min_qty) equal to the values you get back.