Tips for creating complex SQL queries

Question

I started programming sometime with PHP, using MySQL as a database, and I know basic SQL queries to simple JOINS containing up to two tables.

But at that moment when I need to get results from 3 or more tables, I was stuck. No matter how hard I try, I still manage to get lost. I searched everywhere, looking for a good tutorial on how to solve complex SQL queries, but did not find anything that explains how to do this. Most tutorials consist of solutions for a specific problem, but they do not explain the ideal general procedure for how to solve a problem.

Can anyone explain the basic general way to go from start to finish, how to build a query, etc. when it comes to complex queries.

For instance:

I have a forum with the following database structure:

forumCategory:

id | name | desc 

forumTopic

 id | category_id | created_on | created_by | title | content 

forumPost

 id | topic_id | created_on | created_by 

Users

 id | first_name | last_name 

All topics are created in the forumTopic table. All answers to this topic are inserted into the forumPost table.

Now on the main page of the forum I need to display categories, the last message sent by the user in this particular category, the user who posted the last message.

The thread I was thinking about was:

• Find the last post in the category by looking at MAX (id) in the forumPost table, grouped by topic_id. This will give me the ID of the last post in each topic.

• Now again, find the MAX (from the IDS I received earlier), grouped by category_id. This will give me the last message in each category.

Identifiers automatically activate primary keys.

But I am stuck in building an SQL query from the above algorithm

It would be very helpful if someone could help me with this.

Answer 1

Get the latest post for each category by attaching a post to a 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 step for explanatory purposes - it is included in the request below)

Then attach this to the user table to find out the username (presumably the message has a user id?)

 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 might want to update the category table with the last message each time a message is created. This way you can run a much simpler query for your forum homepage.