Page 1 of 1

Advanced query

Posted: Mon Jan 10, 2011 6:56 am
by AGISB
I am building a forum just for the fun of doing so and just because I like to have my own litte one without any hassles of overkill:

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
I find the LIMITs particularly problematic as they are mysql only and would not permit db conversion

Re: Advanced query

Posted: Wed Jan 12, 2011 2:27 pm
by VladSun
I seems to me your query (or DB design) is a big mess :)
Please, post you table structures and explain again what you're trying to fetch.