Solved

what wrong in mysql query syntax?

Posted on 2014-03-25
8
813 Views
Last Modified: 2014-03-25
Hi all,

I need help to fix the MySQL query. I am using this query with node.js technology.

'Select * from (select categoryId,categoryName,categoryImage,smaatzCount,"YES" as SubCategoryAvailable from (Select categoryId, categoryName, categoryImage, coalesce(Count,0) as smaatzCount from category left join (SELECT count(SmaatzId) as Count, CategoryId as catId from smaatzcategory group by CategoryId) as tt on category.categoryId = tt.catId Where category.ParentId is Null) as categories where categoryId in (select ParentId from category) union all select categoryId,categoryName,categoryImage,smaatzCount,"NO" as SubCategoryAvailable from (Select categoryId,categoryName,categoryImage,coalesce(Count,0) as smaatzCount from category left join (SELECT count(SmaatzId) as Count, CategoryId as catId from smaatzcategory group by CategoryId) as tt on category.categoryId = tt.catId Where category.ParentId is Null) as categories where not exists (select ParentId from category where categories.categoryId = category.ParentId)) A order by categoryId'

Open in new window


the above query works perfect but when i tried below query it throws error like this_
"Error: ER_PARSE_ERROR: 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 '? AND category.ParentId is Null) as categories where not exists (select ParentId' at line 1"

New query:

"Select * from (select categoryId,categoryName,categoryImage,smaatzCount,'YES' as SubCategoryAvailable from (Select categoryId, categoryName, categoryImage, coalesce(Count,0) as smaatzCount from category left join (SELECT count(SmaatzId) as Count, CategoryId as catId from smaatzcategory group by CategoryId) as tt on category.categoryId = tt.catId Where category.DBID=? AND category.ParentId is Null) as categories where categoryId in (select ParentId from category) union all select categoryId,categoryName,categoryImage,smaatzCount,'NO' as SubCategoryAvailable from (Select categoryId,categoryName,categoryImage,coalesce(Count,0) as smaatzCount from category left join (SELECT count(SmaatzId) as Count, CategoryId as catId from smaatzcategory group by CategoryId) as tt on category.categoryId = tt.catId Where category.DBID=? AND category.ParentId is Null) as categories where not exists (select ParentId from category where categories.categoryId = category.ParentId)) A order by categoryId",[dbid]

Open in new window


Here [dbid] is passed value for category.DBID=?
0
Comment
Question by:KRUNAL TAILOR
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 34

Expert Comment

by:ste5an
ID: 39952828
Run your query in a MySQL client. The syntax looks fine.
0
 
LVL 12

Author Comment

by:KRUNAL TAILOR
ID: 39952856
Thanks ste5an

ya on client it is working fine but when i tried with node.js api it throws the above error. Some syntax error. but i am not getting where i made mistake.

Thanks & Regards,
Krunal T. Tailor
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39952867
The error message seems to indicate you are passing in a parameter which I don't see in your code, notice the "near ?" in that error message.

But the sql you have provided does not contain that parameter.

Where/how does this apply?
     Here [dbid] is passed value for category.DBID=?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 34

Expert Comment

by:ste5an
ID: 39952876
I would guess, it depends entirely what are you doing in your "node.js" code. Post a concise and complete example.
0
 
LVL 36

Accepted Solution

by:
mccarl earned 500 total points
ID: 39953186
.....ParentId)) A order by categoryId",[dbid]
I don't really know node.js but I would assume that the problem might be that you are using 2 ? placeholders but are only providing 1 parameter.  Can you try this...
.....ParentId)) A order by categoryId",[dbid],[dbid]

Open in new window

0
 
LVL 12

Author Closing Comment

by:KRUNAL TAILOR
ID: 39955012
Thank you mccarl for your help.
0
 
LVL 12

Author Comment

by:KRUNAL TAILOR
ID: 39955013
Thanks for all, all of this information was very helpful.
0
 
LVL 36

Expert Comment

by:mccarl
ID: 39955014
Your welcome! :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question