How do I get the latest post for each category in this forum using MySQL?

Question

EDIT:

Let me be more specific in what I'm after:

catID, cat1, cat2, cat3, cat4, pri_color,sec_color, and cat_name are all related to each specific category.

The sum_views field should correspond to the sum of all views in the forum for that particular category. The count_posts field should correspond to the number of posts in the forum for that category.

The userID, forum_id, title, alias, created, and paragraph correspond to the newest post in each category.

So in other words, for each category, I need the corresponding category information, the aggregate forum statistics for each category, and finally, the newest post in each category.

---

I've been given a small project to create a forum type view for our existing system. In this case, I need to find the newest post (and other information) in each forum category.

My current query is as follows:

SELECT DISTINCT forum.catID AS catID, category.cat1 AS cat1, 
category.cat2 AS cat2, 
category.cat3 AS cat3, 
category.cat4 AS cat4, 
SUM(forum.view) AS sum_views, 
COUNT(forum.id) AS count_posts, 
category.pri_color AS pri_color, 
category.sec_color AS sec_color, 
category.name AS cat_name, 
forum.userID AS userID, 
forum.id AS forum_id, 
forum.title AS title, 
users.alias AS alias, 
MAX(forum.created) AS created, 
forum.paragraph AS paragraph 
FROM forum, category, users 
WHERE forum.approved = 'yes' 
AND users.id = forum.userID 
AND forum.catID = category.id 
GROUP BY forum.catID 
ORDER BY category.name

And it gives me almost all the correct information I want EXCEPT the actual newest post. I suppose my main culprit is my inexperience with JOINS and GROUP BY. It seems to be grouping the data in such a way that it gives me the newest created timestamp but the oldest forum post.

Note that for now, I cannot change the table structure or create a cache table in the current software, though we will be building a replacement in the near future. Also, the id field in the USERS table is a foreign key for another table.

---

FORUM

id   int(10) unsigned NO PRI NULL auto_increment
userID   int(10) unsigned NO MUL 0
catID    int(3)  unsigned NO MUL 0
regID    int(3)  unsigned NO MUL 0
approved    enum('yes','no') NO MUL yes
title    varchar(150) NO MUL paragraph text NO NULL
view     int(10) unsigned NO 1
created     int(10) unsigned NO 0
modified    int(10) unsigned NO MUL 0
ip   varchar(15) NO
oldID    int(10) unsigned NO 0
comments    int(4) NO MUL 0
responses   int(4) NO 0
pics     int(4) NO MUL 0

USERS

user_id     int(10) unsigned NO PRI NULL auto_increment   
id   int(10) unsigned NO MUL 0     
alias    varchar(50) NO MUL new     
email    varchar(150) NO MUL       
fname    varchar(30) NO MUL       
lname    varchar(30) NO MUL       
address     varchar(200) NO         
city     varchar(50) NO         
state    varchar(50) NO         
zip      varchar(20) NO         
country     varchar(50) NO         
job      varchar(150) NO         
phone    varchar(30) NO         
url      varchar(200) NO         
pref_news   enum('yes','no') NO   no     
pref_contact    enum('yes','no') NO   yes     
pref_summary    int(3) NO   20     
pref_showLoc    enum('yes','no') NO   no     
pref_showName   enum('yes','no') NO   no     
pref_showEmail  enum('yes','no') NO   no     
pref_showUrl    enum('yes','no') NO   no

CATEGORY

id   int(10) unsigned NO PRI NULL auto_increment
area     enum('article','classifieds','news','forum') NO   forum   
level    enum('1','2','3','4') NO   1   
cat1     int(10) NO   0   
cat2     int(10) unsigned NO   0   
cat3     int(10) unsigned NO   0   
cat4     int(10) unsigned NO   0   
name     varchar(150) NO       
pri_color   varchar(6) NO       
sec_color   varchar(6) NO       
right_nav   text NO   NULL   
left_ad     text NO   NULL   
footer_ad   text NO   NULL   
top_ad   text NO   NULL

Answer 1

Your query (reformatted for clarity):

   SELECT DISTINCT f.catID AS catID, 
                    c.cat1, c.cat2, c.cat3, c.cat4, 
                    SUM(f.view) AS sum_views, 
                    COUNT(f.id) AS count_posts, 
                    c.pri_color, c.sec_color, c.name AS cat_name,
                    f.userID AS userID, 
                    f.id AS forum_id, f.title, u.alias, 
                    MAX(f.created) AS created, 
                    f.paragraph
      FROM forum f JOIN users u ON (u.id = f.userID)
                   JOIN category c ON (f.catID = c.id)
      WHERE f.approved = 'yes'
    GROUP BY f.catID ORDER BY c.name

The issue with your query is that the aggregated data cannot be logically related to the unaggregated data. For example, the aggregated count for a particular category does not apply to any particular user. So either you want to get your aggregate data separately or you also want to group by user info:

SELECT c.name,c.pri_color, c.sec_color,
       cats.ualias,
       cats.sum_views, cats.count_posts, cats.created
       c.cat1, c.cat2, c.cat3, c.cat4,
FROM categories c JOIN
    (SELECT f.catID, u.alias AS ualias
           SUM(f.view) as sum_views, 
           COUNT(f.id) as count_posts, 
           MAX(f.created) as created
    FROM forum f JOIN users u ON (f.userID=u.id)
    WHERE f.approved='yes'
    GROUP BY f.catID, u.alias) AS cats
ON (c.id=cats.catID);

Answer 2

Had an issue with account creation which won't let me edit my post, but...

This query:

SELECT DISTINCT category.id, category.cat1, category.cat2, category.cat3, category.cat4, category.pri_color, category.sec_color, SUM(forum.view) AS sum_views, COUNT(forum.id) AS count_posts FROM category, forum WHERE forum.catID = category.id AND category.area = 'forum' AND forum.approved = 'yes' GROUP BY category.id

effectively takes care of all of the query dealing with the category table information. Now I just need to know how to link it to the latest post for each category.