[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

cfquery error

I'm receiving an error when running the following code (the db is in mysql):

<!--- Get Categories --->
> <CFQUERY NAME="GetCategories" DATASOURCE="#maindatabase#">
> SELECT category.acategory, count(publications.pubid) AS [count],
> category.acatid
> FROM editors INNER JOIN (publications INNER JOIN category ON
> publications.acatid = category.acatid) ON
> editors.editorid = publications.editorid
> GROUP BY category.acategory, category.acatid;
> </CFQUERY>


Error:
[MySQL][ODBC 3.51 Driver][mysqld-4.0.24-nt]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 '[count], category.acatid FROM editors INNER JOIN (publications

SQL = "SELECT category.acategory, count(publications.pubid) AS [count], category.acatid FROM editors INNER JOIN (publications INNER JOIN category ON publications.acatid = category.acatid) ON editors.editorid = publications.editorid GROUP BY category.acategory, category.acatid;"

Data Source = "LIBRARY"

Any ideas?
0
bluskyGuy
Asked:
bluskyGuy
2 Solutions
 
Renante EnteraCommented:
Hi bluskyGuy!

Try changing your alias with another name which is not a reserved word.

Something like this :

<CFQUERY NAME="GetCategories" DATASOURCE="#maindatabase#">
SELECT
  category.acategory, count(publications.pubid) AS counter, category.acatid
FROM editors
INNER JOIN (publications INNER JOIN category ON publications.acatid = category.acatid)
  ON editors.editorid = publications.editorid
GROUP BY category.acategory, category.acatid;
</CFQUERY>

Hope this helps you.  Just try it then let me know the result.


Goodluck!
eNTRANCE2002 :-)
0
 
Billy_NastyCommented:
like eNTRANCE2002 said don't use reserved words
and don't use the [] signs round you alias name.

think that will do

greets
billy
0
 
rob_lorentzCommented:

try this....

SELECT  category.acategory,
            count(publications.pubid) AS pubCount,
            category.acatid
FROM editors
  INNER JOIN publications ON editors.editorid = publications.editorid
  INNER JOIN category ON publications.acatid = category.acatid
GROUP BY category.acategory, category.acatid
0
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 
trailblazzyr55Commented:
you could try this as well, not sure how the other queries have worked for you, but just another idea :o)

SELECT
  cat.acategory,
  cat.acatid,
  count(pub.pubid) AS pCount

FROM editors edi JOIN
  (publications pub JOIN category cat ON pub.acatid = cat.acatid)
  ON edi.editorid = pub.editorid

GROUP BY cat.acategory, cat.acatid     <----didn't need the extra ';' either

~trail
0
 
bluskyGuyAuthor Commented:
OK, the query seems to work correctly except for the fact that he <CFOUTPUT QUERY="GetCategories">
<LI><a href="./list.cfm?acatid=#acatid#">#acategory#</A> (#GetCategories.RecordCount#)
</cfoutput>

Isn't outputting the correct number of results for each primary category...all of the RecordCount numbers are the same. Any idea how to get the correct numbers displaying?

Currently the data is outputting like so:
# Life:Womens Issues (94)
# Marketing:Advertising (94)
# Marketing:Off-line/Telemarkt (94)
# Marketing:On-line (94)
# Marketing:PR/Media (94)
# Marketing:Sales (94)
# Marketing:Shoestring (94)
# News (94)
# News:Current Events (94)
# Political:Conservative (94)
# Political:Liberal (94)
# Publishing:Self-Publishing (94)
# Travel/Tourism (94)
# Trivia:Kids (94)


I need it to display the correct numbers to the right.

Thanks,
Rick
0
 
bluskyGuyAuthor Commented:
Nevermind, I figured it out...forgot to reference the #pubcount# var :P

Thanks!
-Rick
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now