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 4 of 4
  1. #1
    Regular Coder
    Join Date
    Aug 2002
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Left join to get the 1 most recent

    Firstly table structure - 1 user has multiple status records. When a new status record is created it gets the current datetime inserted into 'date_created'.

    user
    user_id
    site_id

    status
    status
    notes
    date_created


    I wish to display a list of all users and show beside them their current/most recent status record. Here is my code currently:

    Code:
    SELECT user.*, status.date_created, status.status, status.notes,
    DATE_FORMAT(status.date_created,'%l:%i %p') as last_updated_time, 
    DATE_FORMAT(status.date_created,'%e/%c') as last_updated_date 
    FROM user 
    LEFT JOIN status ON status.user_id = user.user_id
    WHERE user.site_id = 1 
    GROUP BY user.user_id
    ORDER BY 
    CASE user.user_id WHEN 8 THEN 0 ELSE user.user_id END

    Currently via the LEFT JOIN and GROUP this does display a list of all users with 1 status record per user but it's simply the first status record in the table (ordering by id i guess), not the most recent.

    How can I get it to display the most recent instead, based on date_created. I've tried adding ordering to the left join but this returned an error.


    Last edited by quadrant6; 12-09-2006 at 10:39 PM.

  • #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
    Your group by clause is incorrect, see GROUP BY HIDDEN FIELDS in the manual to see why.

    as for your question, if you are trying to match something don't use an outer join, that returns unmatched as well as matched rows so use an inner join.

    and the answer you would most likely use is a co-related subquery something along the lines of:

    Code:
    select
    user.user
    user.user_id
    user.site_id
    
    status.status
    status.notes
    status.date_created
    from user inner join status
    on user.user_id = status.status_id
    where 
    status.date_created =
      (select max(dt.date_created) from yourtable as dt
        where dt.user_id = user.user_id)

  • #3
    Regular Coder
    Join Date
    Aug 2002
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the reply, I tried out your example and further queries using subquery method but all failed. I've just discovered the mysql version we are using is 4.0 so I won't be able to use these.

  • #4
    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
    well you should upgrade if you have the ability to do so. if you are paying a host then you should find another. mysql 4.0.x was replaced in October 2004.


  •  

    Posting Permissions

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