Huge SELECT Query : Can't Wrap My Head Around It

[GroZZleR]

Hey all,

 

I\'m writing some custom message board software as a learning experience.

 

I\'ve got 4 tables in total:

FORUM_CATEGORIES, FORUM_BOARDS, FORUM_TOPICS and FORUM_POSTS

 

As long as an ACCOUNTS table.

 

I\'m working on the part of the page that renders all the available topics, and then gives statistics about them. Such as, number of replies, creator of the topic, person who last replied and the date they last replied.

 

So basically, what I need to do is this:

Count the number of entries in FORUM_POSTS that belong to FORUM_TOPICS and this will give me the number of replies.

 

FORUM_TOPICS stores the ID_Account of the matching ID from ACCOUNTS, thats how I\'ll get the person\'s username who posted.

 

Then I need to find the LAST FORUM_POST which matches the ID of FORUM_TOPICS, this will give me the information I need (Date and ID_Account) that I then I need look up.

 

And then on top of that, I need to order the FORUM_TOPICS in such a way, that the one last replied to, is highest on the list and it descends from there (like any forum, the last replied to topic is at the top).

 

Heres a link to a text file which has my MySQL tables:

http://www.feudalconquest.com/fuckem/MySQL.txt

 

--------------

 

Okay so theres the problem.

 

Now, heres what I\'ve been using as a query. The problem is, it returns to me as 3 different rows, when I only want 2. (Its returning an extra row from POSTS, I just want the _LAST_ post to match up with the current topic so I can order them like that.) And it also doesn\'t return the last person to reply, just the person who started the thread nor does it count how many replies there are to a specific topic.

 

$sQuery = sprintf(\" SELECT FORUM_TOPICS.*, ACCOUNTS.Account_Name, FORUM_POSTS.Date

FROM ACCOUNTS

LEFT JOIN FORUM_TOPICS ON (FORUM_TOPICS.ID_Account = ACCOUNTS.ID)

LEFT JOIN FORUM_POSTS ON (FORUM_TOPICS.ID = FORUM_POSTS.ID_Topic)

WHERE FORUM_TOPICS.ID_Board=\'%d\' ORDER BY FORUM_POSTS.Date DESC\", $_GET[\'ID_Board\']);

 

 

Anyone think they can gimmie a hand here? I\'m so lost.