In article <200809070253.15422.uwe@oss4u.com>,
"Uwe C. Schroeder" <uwe@oss4u.com> writes:
> or maybe not and I'm just not getting it.
> So here's the scenario:
> I have 3 tables
> forum: with primary key "id"
> forum_thread: again primary key "id" and a foreign key "forum_id" referencing
> th primary key of the forum table
> forum_post: again primary key "id" with a forign key "thread_id" referencing
> the primary key of the forum_thread table
> The forum_post table also has a field "date_posted" (timestamp) with an index
> on it.
> What I need is an efficient way to create overviews (just think about a forum)
> I.e. the forum table has 3 records, one for each forum category
> I want to get a list looking like
> forum id thread_id post_id
> 1 6 443
> 2 9 123
> 3 3 557
> The trick is, that I need the latest post (by the date posted column) for each
> category (speak forum_id). Due to the keys the forum_thread table has to be
> involved.
> I've been thinking about this for hours now, but I just can't come up with a
> query that will give me 3 records, one for each category showing the latest
> post.
Try something like this:
SELECT t1.forum_id, p1.thread_id, p1.id AS post_id, p1.date_posted
FROM forum f1
JOIN forum_thread t1 ON t1.forum_id = f1.id
JOIN forum_post p1 ON p1.thread_id = t1.id
LEFT JOIN (
SELECT t2.forum_id, p2.thread_id, p2.date_posted
FROM forum_thread t2
JOIN forum_post p2 ON p2.thread_id = t2.id
) AS f2 ON f2.forum_id = f1.id AND f2.date_posted > p1.date_posted
WHERE f2.forum_id IS NULL
ORDER BY t1.forum_id
