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

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 foreig开发者_如何学运维n 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   

---

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);

---

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.