Join on latest row only

Dev Shed Forums Sponsor:

February 1st, 2013, 04:25 AM

qwertyjjj

Contributing User

Join Date: Jan 2010

Posts: 75

Time spent in forums: 15 h 7 m 39 sec
Reputation Power: 4

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

February 1st, 2013, 09:27 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 33 sec
Reputation Power: 4140

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'

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

Viewing: Dev Shed Forums > Databases > MySQL Help > Join on latest row only

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump