L.S.

Could anyone help me with my SQL query? The tables I need for my query:

tblUser
-------
u_id
u_nickname
[...]

tblTopicComment
---------------
tc_id
tc_topicId
tc_userId
tc_contentbody
tc_insertdate
[...]

I want to select:
a) the most recent comments on a specific topic AND
b) from different users

I now use:

SELECT tc_contentbody, UNIX_TIMESTAMP(tc_insertdate), u_nickname FROM tblTopicComment INNER JOIN tblUser on (tc_userId = u_id)
GROUP BY tc_userId
ORDER BY tc_insertdate DESC LIMIT 3

This query doesn't seem to grab the most recent comments. I've also tried using the DISTINCT, but that didn't work for me either.

Any suggestions?


Thanks in advance,
MvO

Try this:
SELECT tc_contentbody, tc_insertdate, u_nickname
FROM tblUser, tblTopicComment
WHERE u_id = tc_userId
ORDER BY tc_insertdate DESC
LIMIT 3;

Hi jmaronilla,

Thanks for your comment. But with your solution I wouldn't retrieve the most recent comments "from different users". I've found the following solution

SELECT tblTopicComment.tc_contentbody, tblTopicComment.tc_insertdate, tblUser.nickname
FROM tblTopicComment
LEFT JOIN tblUserON ( tblTopicComment.tc_userId = tblUser.u_id )
WHERE tblTopicComment.tc_id
IN (
SELECT MAX( tblTopicComment.id ) AS last_comment
FROM tblTopicComment
GROUP BY tc_userId
ORDER BY last_review DESC
)
ORDER BY tc_insertdate DESC

Solved! Thanks.

Your solution with field last_review was not originally on your field list for your criteria that is why I did not know. Just thought my sql syntax will make it simplier. Anyway you got your solution to your problem. Cheers!!!

Sorry for the misunderstanding and thanks a lot for your comment!