I got two tables, conversations and messages, What I’d like to do is to make a inner join from conversations to messages.
Here is my query:
x
SELECT
messages.msg,
messages.`read`,
conversations.userid,
conversations.contactid
FROM conversations
INNER JOIN messages ON
conversations.id = messages.convId
WHERE conversations.id IN(443,444)
Now everything works as it should, but a last thing is that in the inner join where i use
conversations.id = messages.convId
I would like to get the highest ids only, like:
AND MAX(messages.id)
But that doesn’t work
EDIT: I tried once to use:
LEFT JOIN messages
ON conversations.id = messages.convId
AND messages.id = MAX(messages.id)
But i got an error saying: Invalid use of group function.
Advertisement
Answer
EDIT
This will work!
SELECT conversations.*, m1.*
FROM conversations
LEFT JOIN messages m1
ON conversations.id = m1.cid
AND m1.id = (
SELECT MAX(m2.id)
FROM messages m2
WHERE m2.cid = conversations.id
)