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;

[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?
Who is Participating?
rob_lorentzConnect With a Mentor Commented:

try this....

SELECT  category.acategory,
            count(publications.pubid) AS pubCount,
FROM editors
  INNER JOIN publications ON editors.editorid = publications.editorid
  INNER JOIN category ON publications.acatid = category.acatid
GROUP BY category.acategory, category.acatid
Renante EnteraConnect With a Mentor Senior PHP DeveloperCommented:
Hi bluskyGuy!

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

Something like this :

<CFQUERY NAME="GetCategories" DATASOURCE="#maindatabase#">
  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;

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

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

think that will do

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

  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

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#)

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.

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.