Get the first and last posts in a thread
Posted
by
Grampa
on Stack Overflow
See other posts from Stack Overflow
or by Grampa
Published on 2012-09-01T21:08:07Z
Indexed on
2012/09/01
21:38 UTC
Read the original article
Hit count: 197
I am trying to code a forum website and I want to display a list of threads. Each thread should be accompanied by info about the first post (the "head" of the thread) as well as the last. My current database structure is the following:
threads table:
id - int, PK, not NULL, auto-increment
name - varchar(255)
posts table:
id - int, PK, not NULL, auto-increment
thread_id - FK for threads
The tables have other fields as well, but they are not relevant for the query. I am interested in querying threads
and somehow JOIN
ing with posts
so that I obtain both the first and last post for each thread in a single query (with no subqueries). So far I am able to do it using multiple queries, and I have defined the first post as being:
SELECT *
FROM threads t
LEFT JOIN posts p ON t.id = p.thread_id
ORDER BY p.id
LIMIT 0, 1
The last post is pretty much the same except for ORDER BY id DESC
. Now, I could select multiple threads with their first or last posts, by doing:
SELECT *
FROM threads t
LEFT JOIN posts p ON t.id = p.thread_id
ORDER BY p.id
GROUP BY t.id
But of course I can't get both at once, since I would need to sort both ASC
and DESC
at the same time.
What is the solution here? Is it even possible to use a single query? Is there any way I could change the structure of my tables to facilitate this? If this is not doable, then what tips could you give me to improve the query performance in this particular situation?
© Stack Overflow or respective owner