Tips for building complex SQL queries
I have started programming for sometime with PHP using MySQL as the database and I know basic SQL queries upto simple JOINS containing upto two tables.
But the moment I need to get results from 3 or more tables, I am stuck. No matter how hard I try, I still manage to find myself lost. I have searched everywhere looking for a good tutorial on how to tackle complex SQL queries but haven't found anything that explains how to go about it. Most tutorials consists of solutions for a particular problem, but they don't explain the perfect general procedure how to go about tackling a problem
Can anyone explain the basic general way of going about from start to finish, how to construct the query, etc when it comes to complex queries.
I have a forum with the following database structure:
id | name | desc
id | category_id | created_on | created_by | title | content
id | topic_id | created_on | created_by
id | first_name | last_name
All topics are created in the forumTopic table. All replies to that topic are inserted in the forumPost table.
Now on the forum main page, I need to display the categories, the very last post posted by a user in that particular category, the user who posted the last post.
The flow I thought of was :
Find the last post in a category by looking at the MAX(id) in the forumPost table grouped by topic_id. This will get me the ID of the last post in every topic.
Now again find the MAX(of the IDS I got earlier) grouped by category_id. This will get me the last post in every category.
IDs are autoincrementing primary keys.
But I got stuck with constructing a SQL query from the above algorithm
Would be very helpful if someone could help me out on this one.
Get the last post for each category by joining Post to Topic
SELECT category_id , category.name, Max(ForumPost.ID) as maxpostid from ForumPost inner join ForumTopic on ForumPost.Topic_ID = ForumTopic.ID inner join ForumCategory on ForumTopic.Category_Id = ForumCategory.ID group by category_Id, category.name
(This is an intermediate stage for explanatory purposes - it is included in the query below )
Then join this to the user table to find out the user name (presumably a post has a userid?)
select users.name, lastposts.* from forumpost inner join ( SELECT category_id , category.name, Max(ForumPost.ID) as maxpostid from ForumPost inner join ForumTopic on ForumPost.Topic_ID = ForumTopic.ID inner join ForumCategory on ForumTopic.Category_Id = ForumCategory.ID group by category_Id, category.name ) lastposts on forumpost.id = lastposts.maxpostid inner join users on forumpost.userid =users.id
However, you may want to consider updating the category table with the last post each time a post is made. That way you can run a much simpler query for your forum front page.