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.

For example:

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 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 :

  1. 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.

  2. 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.

Answers


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.


Need Your Help

How to make a stable Durandal router URL

html single-page-application durandal sammy.js

I'm using durandal to make a SPA website. Thing is, the URL not not stable everytime.