FROM coaching_threads AS p
JOIN ( SELECT coaching_thread_id
, MAX(created) AS max_created
BY coaching_thread_id ) AS m
ON m.coaching_thread_id = t.id
JOIN coaching_posts AS p
ON p.coaching_thread_id = t.id
AND p.created = m.max_created
by the way, why are created and modified both NOT NULL?
I'm using a PHP framework (CakePHP), which doesn't support OUTER JOINS, SUB SELECTS, etc., so I'd have to use a custom query, which would make paging etc. more difficult, but that's not the end of the world.
created and modified are NOT NULL because as soon as the record is created, it should have non-null dates in those two fields. A NULL value would have no meaning.
Thanks Rudy. Yes, you can write finder queries and custom queries, but I'm not sure how that will affect record paging and other framework functionality.
The query you wrote though didn't return the expected results.
Another option is to maintain an extra field in the thread table with the date of the last post ... I started with this but maintaining it was confusing when records were deleted, etc. ... I may have to return to this.
Do you know the answer to my question in post #6 above?