Error Executing Database Query

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'AND'    


-------------------------------------------------------------

 SELECT departments.pos, departments.depId, departments.name as depName, departments.metaDescription as depMeta, categories.name as catName, categories.metaDescription as catMeta, catId
        FROM departments
        LEFT JOIN categories on departments.depId = categories.depId ORDER BY pos ASC
LVL 12
pigmentartsAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
PLEASE post the full query...

there must be an and in it somewhere...

(ps... please alias your table names it makes the sql much more readable...)
 
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with lowfatspread: the query you posted does not contain an AND anywhere, so you must have another SQL that raises the error.
0
 
pigmentartsAuthor Commented:
this is the whole query, i posted the wrong error sorry

 [Macromedia][SQLServer JDBC Driver][SQLServer]Ambiguous column name 'pos'.


-----------------------------


<cffunction name="getMenuDepartments" returntype="query" hint="get departments and categories">
     <cfset var locals = StructNew()>
     <cfquery name="locals.departments" datasource="#dbSource#" username="#dbUsername#" password="#dbPassword#">
        SELECT departments.pos, departments.depId, departments.name as depName, departments.metaDescription as depMeta, categories.name as catName, categories.metaDescription as catMeta, catId
        FROM departments
        LEFT JOIN categories on departments.depId = categories.depId ORDER BY pos ASC      
       </cfquery>
     
<cfreturn locals.departments>
</cffunction>      
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
pigmentartsAuthor Commented:
ps, sorry if its a little hard to read was cut and paste happy. i think the error is becuase it was in mysql now i have a ms msql database does it have to be in tsql? ect

0
 
pigmentartsAuthor Commented:
the order by is the problem (ORDER BY pos ASC) but in tsql i dont know how to correct this without putting every column name.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
       SELECT departments.pos, departments.depId, departments.name as depName, departments.metaDescription as depMeta, categories.name as catName, categories.metaDescription as catMeta, catId
        FROM departments
        LEFT JOIN categories on departments.depId = categories.depId ORDER BY departments.pos ASC      
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
explanation: as you have a "pos" column in both tables, it would not be able to know which to use.
YOU might know that both values are the same, but SQL cannot know.

as lowfatspread indicated already, use table alias names, makes the query easier to read:

       SELECT d.pos, d.depId, d.name as depName, d.metaDescription as depMeta, c.name as catName, c.metaDescription as catMeta, c.catId
        FROM departments d
        LEFT JOIN categories c on d.depId = c.depId ORDER BY d.pos ASC  
0
 
pigmentartsAuthor Commented:
i see so i dont have to list them all just do departments.pos. many thanks
0
 
pigmentartsAuthor Commented:
i will use use table alias names now, i see what you mean
0
All Courses

From novice to tech pro — start learning today.