View Full Version : SQL INNER JOIN Issue

09-12-2011, 01:09 AM
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:


$query1 = mysql_query("SELECT *
FROM products
(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!

09-12-2011, 04:53 PM
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.