Thursday, 22 August 2013

MySQL: Join three tables and show total count

MySQL: Join three tables and show total count

I have the following three tables (simplified):
User (
UserID
UserName
)
UserMsg (
toUserID
MsgID
theMsg
)
UserMsgComment (
CommentID,
MsgID,
theComment
)
And want to display the UserMsg-s each User has alongside with the count
of the UserMsgComment of each UserMsg. If there is no UserMsgComment for a
UserMsg, I want to show '0'.
UserName | UserMsg | COUNT(UserMsgComment) or show 0
SELECT u.*, m.theMsg FROM User u,
JOIN UserMsg m ON u.UserID = m.toUserID
How do I have to modify this query to show me the total count of
UserMsgComment for each UserMsg or 0 if there is no entry in
UserMsgComment?
Thanks a lot.

No comments:

Post a Comment