what wrong in mysql query syntax?

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=?
LVL 12
KRUNAL TAILORFreelance Mobile DeveloperAsked:
Who is Participating?
 
mccarlConnect With a Mentor IT Business Systems Analyst / Software DeveloperCommented:
.....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
 
ste5anSenior DeveloperCommented:
Run your query in a MySQL client. The syntax looks fine.
0
 
KRUNAL TAILORFreelance Mobile DeveloperAuthor Commented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
PortletPaulfreelancerCommented:
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
 
ste5anSenior DeveloperCommented:
I would guess, it depends entirely what are you doing in your "node.js" code. Post a concise and complete example.
0
 
KRUNAL TAILORFreelance Mobile DeveloperAuthor Commented:
Thank you mccarl for your help.
0
 
KRUNAL TAILORFreelance Mobile DeveloperAuthor Commented:
Thanks for all, all of this information was very helpful.
0
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
Your welcome! :)
0
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.