...

View Full Version : SQL INNER JOIN Issue



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

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
09-12-2011, 03: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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum