[SOLVED] Mysql Join - One result...?

[jackpf]

Hi all,

 

Once again, for my forum, I'm having trouble with this query. I'm trying to get "active users" in a thread, much like this forum does.

 

I have the query working, but I would also like to retrieve the number of posts in this thread by each user, and order users depending on how many posts they have.

 

This is my query thus far:

"SELECT DISTINCT F.`Author`, COUNT(T.`ID`)
FROM `$tb_Forum` F
LEFT OUTER JOIN `$tb_Forum` T ON F.`Author`=T.`Author` AND F.`ID`=T.`Thread`
WHERE F.`ID`='$threadid' OR F.`Thread`='$threadid';"

The `Author` is the author of the post, and $threadid is the ID of the current thread.

 

However, this is only returning one random author, and I don't understand why. If I remove COUNT(T.`ID`) it works fine, but obviously doesn't return the number of posts.

 

Any help would be greatly appreciated,

Cheers.

Jack.

[Mchl]

Remove DISTINCT and add GROUP BY F.`Author`

[JonnoTheDev]

Do you not have an authors table?

You would achieve this in the following mannor:

SELECT a.author, COUNT(f.id) AS totalPosts FROM forum f LEFT JOIN authors a ON (a.authorId=f.authorId) WHERE f.threadId='123' GROUP BY a.authorId ORDER BY totalPosts DESC

[jackpf]

"SELECT F.`Author`, COUNT(T.`ID`) AS `PostCount`
FROM `$tb_Forum` F
LEFT OUTER JOIN `$tb_Forum` T ON F.`Author`=T.`Author` AND F.`ID`=T.`ID`
WHERE F.`ID`='$threadid' OR F.`Thread`='$threadid'
GROUP BY F.`Author`
ORDER BY `PostCount` DESC;"

 

Oh dude, cheers Mchl

 

Works perfectly. Even orders by posts as well

 

Thanks for the reply as well neil.johnson.

 

Nice