Page 1 of 1

quick question on left join query

Posted: Sat Feb 27, 2010 12:43 pm
by scarface222
"SELECT scrusersonline.*, COUNT(scrusersonline.usersonlineid) AS idcount FROM scrusersonline LEFT JOIN topic ON topic.topic_id=scrusersonline.topic_id WHERE topic.subject_id='$type' scrusersonline.usersonlineusrip!='1' AND scrusersonline.id!='0' GROUP BY scrusersonline.topic_id ORDER BY scrusersonline.idcount DESC"

Anyone know why this does not work? without the join it works fine, and I have joined other queries without a problem, the only difference is that I need a count number in this one. If anyone can help me out or give me a tip, I would really appreciate it.

Re: quick question on left join query

Posted: Sat Feb 27, 2010 1:34 pm
by califdon
scarface222 wrote:"SELECT scrusersonline.*, COUNT(scrusersonline.usersonlineid) AS idcount FROM scrusersonline LEFT JOIN topic ON topic.topic_id=scrusersonline.topic_id WHERE topic.subject_id='$type' scrusersonline.usersonlineusrip!='1' AND scrusersonline.id!='0' GROUP BY scrusersonline.topic_id ORDER BY scrusersonline.idcount DESC"

Anyone know why this does not work? without the join it works fine, and I have joined other queries without a problem, the only difference is that I need a count number in this one. If anyone can help me out or give me a tip, I would really appreciate it.
You're telling it to ORDER BY a field of the table scrusersonline that's named idcount, but there is no such field. Just remove the red part.

Re: quick question on left join query

Posted: Sat Feb 27, 2010 1:40 pm
by mikosiko
"SELECT scrusersonline.*, COUNT(scrusersonline.usersonlineid) AS idcount FROM scrusersonline LEFT JOIN topic ON topic.topic_id=scrusersonline.topic_id WHERE topic.subject_id='$type' scrusersonline.usersonlineusrip!='1' AND scrusersonline.id!='0' GROUP BY scrusersonline.topic_id ORDER BY scrusersonline.idcount DESC"
and in addition to that...also maybe because here (in red) something is missing

WHERE topic.subject_id='$type' scrusersonline.usersonlineusrip!='1' AND scrusersonline.id!='0'

Re: quick question on left join query

Posted: Sat Feb 27, 2010 1:46 pm
by califdon
Good catch, mikosiko. I overlooked that omission.

Re: quick question on left join query

Posted: Sat Feb 27, 2010 1:49 pm
by scarface222
lmao I love you guys. I will try to be much more vigilante.

Re: quick question on left join query

Posted: Sat Feb 27, 2010 4:32 pm
by Eran
I think you meant vigilant...

Re: quick question on left join query

Posted: Sat Feb 27, 2010 4:48 pm
by mikosiko
pytrin wrote:I think you meant vigilant...
are you trying to win the "BEST SPELL CHECKER" bumper sticker ? :wink:

bumper sticker = that little thing in your signature :)

Re: quick question on left join query

Posted: Sat Feb 27, 2010 4:55 pm
by Eran
this is not a misspelling, vigilante is a different word..

Re: quick question on left join query

Posted: Sat Feb 27, 2010 6:06 pm
by scarface222
hahahaha pytrin you help me with my code and my grammar, you're my boy on this forum.

vigilant keenly watchful to detect danger; wary: a vigilant sentry.

vigilante any person who takes the law into his or her own hands, as by avenging a crime.

however, I did actually mean to kill criminals and to wrongfully apply the word as an adjective, so you're wrong...

Re: quick question on left join query

Posted: Sat Feb 27, 2010 6:10 pm
by mikosiko
pytrin wrote:this is not a misspelling, vigilante is a different word..
no matter.... the joke was good anyways ... c'mon... smile :D

Re: quick question on left join query

Posted: Sat Feb 27, 2010 6:13 pm
by Eran
however, I did actually mean to kill criminals and to wrongfully apply the word as an adjective, so your wrong...
now, that would be amusing ;)