XF 2.1 Help with SQL Query

[rfc0001]

I'm trying to run a query to ensure the first_post_date is set correctly.

This is what I have currently

SELECT xf_thread.thread_id, xf_thread.first_post_id, post.post_id
FROM xf_thread
LEFT JOIN
(
    SELECT post1.thread_id, post1.post_id
    FROM xf_post post1
    WHERE post1.post_id = (SELECT MIN(post2.post_id) FROM xf_post post2 WHERE post2.post_date = (SELECT MIN(post3.post_date) FROM xf_post post3 WHERE post3.thread_id = post1.thread_id))
) post ON xf_thread.thread_id = post.thread_id
WHERE xf_thread.first_post_id != post.post_id

This appears to work, however if I remove the last line to view all the results, I see NULL for post_id (which correspond to the first post in each thread based on earliest date then earliest post if two posts with the same date). Not sure why I'm getting NULLs for those. Yes, it's a LEFT join so expected to get NULLs if there are not results, however there should be a post_date and post_id for every xf_post and xf_post for every xf_thread, so there is no reason this data shouldn't exist (and I've confirmed it does).

Can someone think of a more elegant SQL query to select the first post for each thread (when sorted ascending by post_date then sorted ascending by post_id where the same post_date) to compare this to the value for thread.first_post_id? Thanks!

 

[rfc0001]

I figured it out:

Detect first_post_id mismatches:

SELECT xf_thread.thread_id, xf_thread.first_post_id, xf_post.post_id
FROM xf_thread
INNER JOIN xf_post  ON xf_thread.thread_id = xf_post.thread_id
AND xf_post.post_id = (SELECT post.post_id
FROM xf_post post
WHERE post.thread_id = xf_post.thread_id
ORDER BY post.post_date ASC
LIMIT 1)
WHERE xf_thread.first_post_id != xf_post.post_id

Detect last_post_id mismatches:

SELECT xf_thread.thread_id, xf_thread.last_post_id, xf_post.post_id
FROM xf_thread
INNER JOIN xf_post  ON xf_thread.thread_id = xf_post.thread_id
AND xf_post.post_id = (SELECT post.post_id
FROM xf_post post
WHERE post.thread_id = xf_post.thread_id
ORDER BY post.post_date DESC
LIMIT 1)
WHERE xf_thread.last_post_id != xf_post.post_id