Re: Query OK in localhost, error on ISP server
Complementing the post above: I have found that SUM(DISTINCT xxx) is valid with v.5x, not so in v.4x. While I am trying to convince my ISP to switch the database to a v.5x server, I would like some help with a workaround - please excuse my newbness. I have tried : (select sum(#variables.tableprefix#threads.views) from #variables.tableprefix#threads where #variables.tableprefix#threads.forumidfk = #variables.tableprefix#forums.id) as views, instead of : sum(distinct #variables.tableprefix#threads.views) as views, but it returns a zero flat instead of, say, 8, (5+3). Thank You. -- View this message in context: http://www.nabble.com/Query-OK--in-localhost%2C-error-on-ISP-server-tp16738784p16753507.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query OK in localhost, error on ISP server
Complementing the post above: I have found that SUM(DISTINCT xxx) is valid with v.5x, not so in v.4x. While I am trying to convince my ISP to switch the database to a v.5x server, I would like some help with a workaround - please excuse my newbness. I have tried : (select sum(#variables.tableprefix#threads.views) from #variables.tableprefix#threads where t#variables.tableprefix#hreads.forumidfk = #variables.tableprefix#forums.id) as views, instead of : sum(distinct #variables.tableprefix#threads.views) as views, but it returns a zero flat instead of, say, 8, (5+3). Thank You. Walter conti contiw wrote: The following query run flawlessly in localhost but produces error on ISP server: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct sf_threads.views) as views, ((count(distinct sf_messages.' at line 6 The actual behaviour here: http://eduforums.us or http://wconti.com/schoolsforums/index.cfm I am using the exact same database both in local and server. Running MySQL 5 in localhost and supposedly versions 4 and 5 sopported by the ISP server. Thanks for helping SELECT #variables.tableprefix#conferences.id, #variables.tableprefix#conferences.name, count(distinct #variables.tableprefix#forums.id)-1 as schools, count(distinct #variables.tableprefix#threads.id) as topics, count(distinct #variables.tableprefix#messages.id) as msgs, sum(distinct #variables.tableprefix#threads.views) as views, ((count(distinct #variables.tableprefix#messages.id) *2) +sum(distinct #variables.tableprefix#threads.views) ) as activity, 0 as hBarLength FROM ((#variables.tableprefix#conferences left JOIN #variables.tableprefix#forums ON #variables.tableprefix#conferences.id = #variables.tableprefix#forums.conferenceidfk) left JOIN #variables.tableprefix#threads ON #variables.tableprefix#forums.id = #variables.tableprefix#threads.forumidfk and #variables.tableprefix#threads.author 'admin') left JOIN #variables.tableprefix#messages ON #variables.tableprefix#threads.id = #variables.tableprefix#messages.threadidfk and #variables.tableprefix#messages.author 'admin' GROUP BY #variables.tableprefix#conferences.id -- View this message in context: http://www.nabble.com/Query-OK--in-localhost%2C-error-on-ISP-server-tp16738784p16753509.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query OK in localhost, error on ISP server
The following query run flawlessly in localhost but produces error on ISP server: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct sf_threads.views) as views, ((count(distinct sf_messages.' at line 6 The actual behaviour here: http://eduforums.us or http://wconti.com/schoolsforums/index.cfm I am using the exact same database both in local and server. Running MySQL 5 in localhost and supposedly versions 4 and 5 sopported by the ISP server. Thanks for helping SELECT #variables.tableprefix#conferences.id, #variables.tableprefix#conferences.name, count(distinct #variables.tableprefix#forums.id)-1 as schools, count(distinct #variables.tableprefix#threads.id) as topics, count(distinct #variables.tableprefix#messages.id) as msgs, sum(distinct #variables.tableprefix#threads.views) as views, ((count(distinct #variables.tableprefix#messages.id) *2) +sum(distinct #variables.tableprefix#threads.views) ) as activity, 0 as hBarLength FROM ((#variables.tableprefix#conferences left JOIN #variables.tableprefix#forums ON #variables.tableprefix#conferences.id = #variables.tableprefix#forums.conferenceidfk) left JOIN #variables.tableprefix#threads ON #variables.tableprefix#forums.id = #variables.tableprefix#threads.forumidfk and #variables.tableprefix#threads.author 'admin') left JOIN #variables.tableprefix#messages ON #variables.tableprefix#threads.id = #variables.tableprefix#messages.threadidfk and #variables.tableprefix#messages.author 'admin' GROUP BY #variables.tableprefix#conferences.id -- View this message in context: http://www.nabble.com/Query-OK--in-localhost%2C-error-on-ISP-server-tp16738784p16738784.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select count(). Help-a-newb
Is it possible to get the count() for forums, threads and messages extrapolating from the following query? Thanx for helping a newb. select sf_conferences.id, sf_conferences.name from ((#variables.tableprefix#conferences left JOIN sf_forums ON sf_conferences.id=sf_forums.conferenceidfk) left JOIN sf_threads ON sf_forums.id = sf_threads.forumidfk) left JOIN sf_messages ON sf_threads.id = sf_messages.threadidfk GROUP BY sf_conferences.id, sf_conferences.name -- View this message in context: http://www.nabble.com/select-count%28%29.-Help-a-newb-tp16582786p16582786.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select count(). Help-a-newb
Thank You Richard! Merci beaucoup. wConti Richard-175 wrote: Hi I think you would do this : SELECT sf_conferences.id, sf_conferences.name, count(*) AS `count` FROM (( LEFT JOIN sf_forums ON sf_conferences.id=sf_forums.conferenceidfk) LEFT sf_threads ON sf_forums.id = sf_threads.forumidfk) LEFT JOIN sf_messages ON sf_threads.id = sf_messages.threadidfk GROUP BY sf_conferences.id, sf_conferences.name; I hope this is what you want :) contiw a écrit : Is it possible to get the count() for forums, threads and messages extrapolating from the following query? Thanx for helping a newb. select sf_conferences.id, sf_conferences.name from ((#variables.tableprefix#conferences left JOIN sf_forums ON sf_conferences.id=sf_forums.conferenceidfk) left JOIN sf_threads ON sf_forums.id = sf_threads.forumidfk) left JOIN sf_messages ON sf_threads.id = sf_messages.threadidfk GROUP BY sf_conferences.id, sf_conferences.name -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/select-count%28%29.-Help-a-newb-tp16582786p16590541.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help-a-new with query ...
select distinct threads.id, threads.name, threads.readonly, threads.active, threads.forumidfk, threads.useridfk, threads.author, threads.datecreated, forums.name as forum, threads.author as username, threads.sticky, threads.views, conferences.name as conference, conferences.id as conferenceid, threads.lastpost, threads.msgcount as messagecount from (threads inner join forums on threads.forumidfk = forums.id) inner join conferences on forums.conferenceidfk = conferences.id where 1=1 cfif arguments.bActiveOnly and threads.active = 1 /cfif cfif isDefined(arguments.forumid) and threads.forumidfk = cfqueryparam value=#arguments.forumid# cfsqltype=CF_SQL_VARCHAR maxlength=35 /cfif == ... as it is now it works fine but I need to add the following condition : cfif isDefined(arguments.testOnly) and arguments.testOnly IS 1 and users.testOnly = cfqueryparam value=#arguments.testOnly# cfsqltype=CF_SQL_SMALLINT /cfif please help with the [select] and [from] (inner join?). Thank you very much. = order by threads.sticky desc, threads.lastpost desc -- View this message in context: http://www.nabble.com/help-a-new-with-query-...-tp16268102p16268102.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
