Sergiu Posted January 19, 2012 Share Posted January 19, 2012 Hi, i have a question regarding an example from ch7, page 220: in order to get the date of the most recent forum, the following query was presented: SELECT f.name, COLESCE (MAX(m.date_entered),'N/A') AS last_post FROM forums AS f LEFT JOIN messages AS m USING (forum_id) GROUP BY (m.forum_id) ORDER BY m.date_entered DESC; The problem is that I can't figure out how this query could be changed in order to find as well the username and subject of the last post, so in the end it will find for each forum: name(of forum)-last_post-username-subject. Any ideas? In short, the structure of the tables is: forums [forum_id, name]; messages [message_id, forum_id, user_id, subject, body, date_entered]; users[user_id, username, pass, first_name, last_name, email]. Link to comment Share on other sites More sharing options...
Larry Posted January 20, 2012 Share Posted January 20, 2012 I *think* you'd want: SELECT f.name, COLESCE (MAX(m.date_entered),'N/A') AS last_post, m.subject, u.username FROM forums AS f LEFT JOIN messages AS m USING (forum_id) LEFT JOIN users AS u USING (user_id) GROUP BY (m.forum_id) ORDER BY m.date_entered DESC; 1 Link to comment Share on other sites More sharing options...
Sergiu Posted January 27, 2012 Author Share Posted January 27, 2012 Thanks for your quick reply. Maybe i do something wrong, but if we take a look at the result, the query does not select the right values. For example, instead of selecting "mysql-friedman-debugging-2012-01-22 14:07", it selects "mysql-troutster-joins- 2012-01-22 14:07", so the username and subject do not match the last posting for each forum section. The max() does the trick in selecting data of the latest post, but i think a similar function is needed for the other columns username and subject. I tried to include a snapshot with the results from mysql client, but i wasn't able to do that, so I just list the query: SELECT f.name, u.username, m.subject, m.date_entered FROM forums AS f LEFT JOIN messages AS m USING ( forum_id ) LEFT JOIN users AS u USING ( user_id ); name username subject date_entered CSS NULL NULL NULL HTML friedman help 2012-01-22 13:47:38 HTML js advice 2012-01-22 14:09:58 MySQL troutster joins 2012-01-22 13:43:33 MySQL friedman aggregate 2012-01-22 13:44:25 MySQL ron_paul aggregate 2012-01-22 13:45:39 MySQL rothbard connect 2012-01-22 13:46:22 MySQL friedman debugging 2012-01-22 14:07:59 PHP friedman GET-POST 2012-01-22 13:46:58 PHP mises forms 2012-01-22 13:49:18 PHP rothbard error 2012-01-22 14:09:01 SELECT f.name, u.username, m.subject, COALESCE( MAX( m.date_entered ) , 'N/A' ) AS last_post FROM forums AS f LEFT JOIN messages AS m USING ( forum_id ) LEFT JOIN users AS u USING ( user_id ) GROUP BY (m.forum_id); name username subject last_post HTML friedman help 2012-01-22 14:09:58 PHP friedman GET-POST 2012-01-22 14:09:01 MySQL troutster joins 2012-01-22 14:07:59 CSS NULL NULL N/A Link to comment Share on other sites More sharing options...
Larry Posted January 28, 2012 Share Posted January 28, 2012 Okay. I'll need to take another crack at it next week, trying on my own database to get it right. Link to comment Share on other sites More sharing options...
Recommended Posts