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 JOINing 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?
