Ch7 Advanced Sql

[Sergiu]

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:

1. forums [forum_id, name];
   
2. messages [message_id, forum_id, user_id, subject, body, date_entered];
   
3. users[user_id, username, pass, first_name, last_name, email].
   

[Larry]

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;

[Sergiu]

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

[Larry]

Okay. I'll need to take another crack at it next week, trying on my own database to get it right.