SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Table joins - selecting max row from joined table

    Hi all,

    I'm sure this was asked before and I search but I'm just not getting it.

    The main query that I want to run is like this (in the real query there are about 30 fields and 10 tables but i think if we can get it working simple it will be easy to roll out):

    Code:
    SELECT a.project_id, a.field1, a.field2, a.field3, f.createdate, f.tstatus, f.createby
    FROM projects a
    LEFT JOIN 'updates' f ON a.project_id = f.project_id
    GROUP BY a.project_id
    For the f. fields, I want all of them from the row that has the most recent date. I would get that from that table by doing this:

    Code:
    SELECT createdate, status, createby, project_id 
    FROM 'updates' 
    WHERE project_id = 'FOR EACH PROJECT' 
    ORDER BY createdate DESC 
    LIMIT 1
    The project id will be in both tables. The one project in projects and have many updates linking to it and I just want the data from the most recent update. Can I use a subquery somehow? I just can't figure it out. Thanks for the help.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT projects.project_id
         , projects.field1
         , projects.field2
         , projects.field3
         , updates.createdate
         , updates.tstatus
         , updates.createby
      FROM projects 
    LEFT OUTER
      JOIN ( SELECT project_id
                  , MAX(createdate) AS createdate
               FROM updates
             GROUP
                 BY project_id ) AS latest
        ON latest.project_id = projects.project_id
    LEFT OUTER
      JOIN updates
        ON updates.project_id = projects.project_id
       AND updates.createdate = latest.createdate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is exactly right, thanks! Worked perfectly! Forgot you could do AND in the join statement. Thanks again!


Bookmarks

Posting Permissions

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