[RESOLVED] Problem I should not be having

rulian

Okay, I admit I havent done mysql in like 5 weeks, but I cant be that rusty
This is suppose to be an easy problem:

Table 1 "Products"
[product_id ] | [title ] .....
1 | blah bah
2 | More blah bah

Problem, I want to establish a quantities history, so I need multiple rows in the quantity table to reflect changes made
I'm trying to now query the products table and attach the values of the quantities table for each product. So I order by datetime to get the newest and want to get each newest row for the corresponding product.

My problem is JOINS return every row, group by must come before order which returns the wrong info, subqueries have returned either no or suffered the same product.

I cant simply query each product_id from the products table as that will create possibly hundred of queries.

This problem doesnt seem that difficult to me but for some reason I'm stuck!

NogDog

All I can think of (and I'm not entirely sure it would work) is a sub-query, which may not be a whole lot more efficient than the separate queries, but at least would only be one query to send through the SQL parser:

SELECT p.product_id, p.title, q.values
FROM Products AS p
INNER JOIN Quantities AS q ON p.product_id = q.product_id
WHERE q.quant_id = (
   SELECT quant_id FROM Quantities 
   WHERE product_id = p.product_id 
   ORDER BY timestamp DESC LIMIT 1
)
ORDER BY p.title

rulian

thanks NogDog
I had to settle to ordering by quant_id desc because using the datetime stamp it was giving me irradical results for some reason