Having clause does not work

Question

I am beginner with SQL, I'm currently trying to use the HAVING CLAUSE but it does not work..

I have two tables :

1. tchat :

2. tchat_message :

So I want the latest messages from the users.

First : I join the tables : `

select user_id, user_message, max(date_message) 
from tchat 
inner join tchat_message on tchat.id=tchat_message.user_id

here it's ok.

Second : I use the having clause :

select user_id, user_message, max(date_message) 
from tchat 
inner join tchat_message on tchat.id=tchat_message.user_id 
group by user_id 
having max(date_message) = date_message`

And here I have an error which says :

Unknown column 'date_message' in 'having clause'

Does anybody have an idea ?

Answer 1

You are using the having clause incorrectly. The following is closer to what you want:

select tm.user_id, tm.user_message, tm.date_message
from tchat t inner join
     tchat_message tm
     on t.id = tm.user_id inner join
     (select user_id, max(date_message) as maxdm
      from tchat_message
      group by user_id
     ) tmm
     on tmm.user_id = tm.user_id and tmm.maxdm = tm.date_message;

You don't need the group by in the outer query. You also don't seem to need the tchat table (I've left it in in case you have additional columns in the select from that table).