[SOLVED] Left Join Order problem

[ted_chou12]

Hello, This is my query:

SELECT t1.*, COUNT(t2.threadid) AS num_replies FROM forum AS t1 LEFT JOIN forum AS t2 ON t1.threadid=t2.threadid AND t2.threadattribute='conthread' WHERE t1.threadattribute='topthread' AND (t1.username='{$r6['username']}' OR t2.username='{$r6['username']}') GROUP BY t1.threadid ORDER BY num_replies

conthread is a reply thread to the starting thread, and top thread is the starting thread, since they are all placed on the same table, so threadattribute separates the two groups. threadid is the unique id to group the topthread and conthread that are of the same thread, eg the starting thread's threadid is 5, then the conthread of the starting thread has threadid 5 as well, a reply to the same starting thread would have a threadid 5 as well. If you start a new thread, the threadid will be a new one, eg. 6.

but there seems to be certain errors in the order, see this page:

http://netfriending.co.cc/forum/usersthreads.php?user=ted_chou12&page=7&order=replyasc

this is suppose to be ordered by the replies from the least to the most, but the middle row, "Hey" thread seems to be misordered from the rest of the others, what may be the problem ???

Thanks,

Ted

[fenway]

Show the actual query, and the actual output, and what's wrong with it.

[ted_chou12]

Show the actual query, and the actual output, and what's wrong with it.

what do you mean? this is the actual query

[fenway]

Show the actual query, and the actual output, and what's wrong with it.

what do you mean? this is the actual query

Not that's php.

[ted_chou12]

Show the actual query, and the actual output, and what's wrong with it.

what do you mean? this is the actual query

Not that's php.

I dont quite understand but I will clarify

<?php $r6['username'] //is some the person's posts that will be showed up
$sql="SELECT t1.*, COUNT(t2.threadid) AS num_replies FROM forum AS t1 LEFT JOIN forum AS t2 ON t1.threadid=t2.threadid AND t2.threadattribute='conthread' WHERE t1.threadattribute='topthread' AND (t2.username='{$r6['username']}' OR t1.username='{$r6['username']}') GROUP BY t1.threadid ORDER BY num_replies";
$read = mysql_query($sql);
?>

It doesnt show up any error, but the order are different than what I wish it to be, after some trials, I found out that it only selects username's conthread instead of all of the conthread in the topthread, so it doesn't have all the number of replies that it is suppose to have.

ie. person A has two replies (two conthread), but the thread has other replies that are from person B or C, so it only groups by the number of replies that person A contributed to the thread, so it doesn't appear right.

the full page of the php code:

http://tedchou12.110mb.com/uploads/usersthreads.txt

 

[fenway]

What I wanted to see what the contents of the $sql after variable interpolation.

 

As for the count, as you say, you are filtering it... if you want to get a different count as well as use this to ORDER the same query, you'd need a sub-query.