February 1st, 2013, 04:25 AM
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
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
FROM wp_posts SA ord
JOIN ( SELECT comment_post_ID
, MAX(comment_post_datetime) AS latest
BY comment_post_ID ) AS last
ON last.comment_post_ID = ord.id
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'