Advanced query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Advanced query

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Advanced query

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply