Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Apr 2011
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts

    SQL INNER JOIN Issue

    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!

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •