[SOLVED] How to grab only latest row in left joined table?

[pepsi_max2k]

Hey all, I have the following data:

 

Table A)

ID, Name, etc

 

Table B)

ID, date, price

 

 

Table A has individual products with unique IDs. Table B has multiple entries for a single ID, primary key is split between ID and date. eg:

 

Table A)
0001, Fish
0002, Beans


Table B)
0001, 2009-01-05, 1.5
0002, 2009-04-02, 5.2
0002, 2009-05-03, 5.7
0001, 2009-04-05, 1.2

 

 

How do I select all entries from table A, while left joining table B and being able to output just the latest price from table B for each ID in table A? Eg:

 

0001, Fish, 1.2
0002, Beans. 5.7

 

So far my code is this:

 

SELECT products.*, prices.*
FROM products 

LEFT JOIN prices 
ON products.id = prices.id
AND prices_ebay.date = (select max(date) from prices) 

GROUP BY products.id";


$num=mysql_numrows($result);

$i=0;
while ($i < $num) {

$id=mysql_result($result,$i,"id");
$pricemin= mysql_result($result,$i,"prices.min");
}

 

Without the AND and GROUP BY, it outputs each product X times for every entry in price with the same ID.

 

With the GROUP BY, it outputs each product only once, but with only the first entry for prices, not the last.

 

With the AND, it only outputs a single price entry for a single product - whatever was the last entry overall in the prices table (not the last one for each product/ID).

 

Thanks for any help.

[pepsi_max2k]

SELECT products.*, prices.*
FROM products

LEFT JOIN prices
ON products.id = prices.id
WHERE prices.date = (select max(prices.date) from prices where prices.id = products.id)";

 

That WHERE part helps a lot, outputting only one row with the latest date, but now it doesn't output anything with no price entries at all which I'd rather it did.

[pepsi_max2k]

Well that wasn't too hard. "OR prices.date IS NULL".

 

Full query is:

 

SELECT products.*, prices.*
FROM products 

LEFT JOIN prices
ON products.id = prices.id
WHERE prices_.date = (select max(prices.date) from prices where products.id = prices.id)
OR prices.date IS NULL

ORDER BY products.id