SQL INNER JOIN Issue

[vjoho]

I'm trying to set up a query that selects the most recent item uploaded from the 4 subcategories (Dresses, Skirts, Shorts, Coats) of the category 'Apparel'. The problem is that the coding below is not limiting the result of 1 of each subcategory and I don't know how to fix that. Below is the code I set up and here's a link to the page I'm having issues with:

http://www.tarimah.com/temp/Apparel.php

$query1 = mysql_query("SELECT *
FROM products
INNER JOIN
(SELECT subcategory as scat, max(date_added) as date
FROM products WHERE category = 'Apparel' group by subcategory) as prod
on products.subcategory = prod.scat and products.date_added = prod.date");

Thanks for reading my post!

[guelphdad]

You can adapt the solution here: http://www.artfulsoftware.com/infotree/queries.php#101
it finds the lowest price per group. you'll need to modify it so it finds the most recent date per group.