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 6 of 6
  1. #1
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts

    Select MAX within join?

    Code:
    SELECT
    tblPageItems.id,
    tblPageItems.item_name,
    tblPageItems.item_category,
    tblItemVersions.html
    FROM tblPageItems
    INNER JOIN tblItemVersions ON tblItemVersions.page_item_id = tblPageItems.id
    If there's more than one entry in tblItemVersions with a particular Item ID - as there will be, this is for a CMS with version control - then the query above will return a row for each version of each item found.

    I don't want that. I just want a row for each Item ID, containing the latest (ie: highest ID) corresponding html field from tblItemVersions. And I'm having a mental block, and can't figure it out. Anyone?

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Sorry - this post has been retracted due to lack of caffeine.
    Last edited by Daemonspyre; 04-10-2007 at 01:37 PM. Reason: Retracted due to lack of caffeine.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #3
    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
    Daemonspyre

    That won't work as your group by clause is incorrect. A group by clause must contain each non-aggregate in the select clause. After you add those you will get more than one row per id.

    Spudhead

    Read this article.

  • #4
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    That's it! Thank you

    ETA:

    Ah. Not quite. I'm still floundering:

    SELECT html FROM tblitemversions as versions
    WHERE versions.id = (
    SELECT MAX(id)
    FROM tblitemversions
    WHERE tblitemversions.page_item_id = versions.page_item_id)

    That gives me the following error:
    You have an error in your SQL syntax near 'SELECT MAX(id) FROM tblitemversions WHERE tblitemversions.page_item_id = version' at line 1
    And that's just a bit to pull the latest version - I've still got to hook it up to the bit that joins the Items table. I'm very confused

    (this is against MySQL 4.0, through phpMyAdmin, on localhost)
    Last edited by Spudhead; 04-10-2007 at 03:22 PM. Reason: incomprehension

  • #5
    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
    upgrade to mysql 4.1 it supports subqueries.

  • #6
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Ah. Yes, that would probably do it. Never mind, it was only a whole day I spent banging my head on that one Thanks for your help


  •  

    Posting Permissions

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