That said I have folloing database setup:
In my post table I have post_id and parentPost which when NULL is the first post of a thread otherwise has the post_id of the first post in it. Now I like to display all threads of a forum and with 1!!! Query I like to get all Info I need to display the forum including the last post_date, ID and username of the poster and the id of the last post in that thread. I know I can do it with multiple queries but I like 1
I got following working solution but I am wandering if I can do easier?
Code: Select all
SELECT a.*,
(SELECT max(b.post_creation) as maxi FROM posts AS b
WHERE a.post_id = b.post_id or a.post_id = b.parentPost) AS wert,
(SELECT d.post_id FROM posts d
WHERE d.post_id = a.post_id OR d.parentPost = a.post_id GROUP BY d.post_id
ORDER BY max( d.post_creation ) DESC LIMIT 0 , 1) AS last_post_id
e.userid as lastuserid, e.username,
FROM
posts AS a, userdata AS e
WHERE
a.parentPost IS NULL and a.forum_id = '**insert ForumID here**' and
e.userid = (SELECT d.userid FROM posts d
WHERE d.post_id = a.post_id OR d.parentPost = a.post_id
GROUP BY d.userid ORDER BY max( d.post_creation ) DESC LIMIT 0 , 1)
Group by a.post_id
ORDER by wert desc