0

I've created my own forum and would like to display
username - from users table
title - form topics table and
a count of comments - from replies table

I 've got the first bit sorted ok

SELECT username, title
FROM users, topics
WHERE users.userid = topics.userid
ORDER BY RAND()

I now want to add a count of the comments associated with each topic, something of the order of

SELECT COUNT(replies.comment) AS 'count'
WHERE topics.topicid = replies.topicID
GROUP BY topics.title

I would have assumed I'd do a LEFT JOIN ON topics.topicid = replies.topicID, but this doesn't seem to work. I've tried various combinations of the query but I can't get it to function. Any ideas.

Regards
Taffd

2
Contributors
4
Replies
5
Views
9 Years
Discussion Span
Last Post by nav33n
0

It's ok folks, I've just cracked it.
Here's the solution

SELECT username, title, COUNT(replies.comment) AS 'count'
FROM users, topics
LEFT JOIN replies
ON topics.topicid = replies.topicID
WHERE users.userid = topics.userid
GROUP BY topics.title
ORDER BY RAND()

0

It's ok folks, I've just cracked it.
Here's the solution

SELECT username, title, COUNT(replies.comment) AS 'count'
FROM users, topics
LEFT JOIN replies
ON topics.topicid = replies.topicID
WHERE users.userid = topics.userid
GROUP BY topics.title
ORDER BY RAND()

Order by rand() ? Thats something new that I am seeing. What does it order by (which column) ?

0

All it does is outputs the results in a random order.
An opening page on one of my forums will show a random 30 topics. People can get a glimpse of what's available and also see various categories listed.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.