Member Avatar for Taffd

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
  • 76 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by nav33n
Member Avatar for Taffd

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()

Member Avatar for nav33n

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) ?

Member Avatar for Taffd

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.

Member Avatar for nav33n

ah! i see.. thanks :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.