#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    81
    Rep Power
    5

    Join on latest row only


    I am trying to join on a row from the wp_comments sta table.
    However this is a status table and contains many rows for each order.
    I only want to check if the comments on the latest row = changed form pending to processing and if so, then I want to bring back a record for the wp_posts ord table.
    If the latest row in wp_comments sta = any other text, then I do not want to bring the record back.
    Any ideas how I can do this? The current SQL brings back everything

    SELECT*
    FROM wp_posts ord
    INNERJOIN wp_comments sta ON ord.ID = sta.comment_post_ID
    WHERE ord.post_type LIKE'shop_order'
    AND sta.comment_content LIKE'%changed from pending to processing%'
    LIMIT 0 , 30
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Code:
    SELECT somthing
         , anything
         , just_not_the_dreaded_evil_seclect_star 
      FROM wp_posts SA ord
    INNER
      JOIN ( SELECT comment_post_ID
                  , MAX(comment_post_datetime) AS latest
               FROM wp_comments
             GROUP
                 BY comment_post_ID ) AS last
        ON last.comment_post_ID = ord.id
    INNER
      JOIN wp_comments AS sta 
        ON sta.comment_post_ID = last.comment_post_ID
       AND sta.comment_post_datetime = last.latest
       AND sta.comment_content LIKE '%changed from pending to processing%' 
     WHERE ord.post_type = 'shop_order'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo