MySql error in your SQL syntax for date

Question

I have just migrated my database from MS SQL to MySQL.

When running reports I now get the following error:

Application Execution Exception
Error Type: database : 0
Error Messages: 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 '2 YEAR(res_created) AS responseYear FROM Responses INNER JOIN ' at line 1

SQL Sent:

SELECT DISTINCT TOP #arguments.yearsToReturn# YEAR(res_created) AS responseYear
        FROM
            Responses
        INNER JOIN
            Questions ON Responses.question_id = Questions.question_id
        INNER JOIN
            Lookup_Survey_Questions ON Questions.question_id = Lookup_Survey_Questions.question_id
        INNER JOIN
            Survey ON Lookup_Survey_Questions.survey_id = Survey.survey_id
        INNER JOIN
            School ON Survey.sch_id = School.sch_id
        INNER JOIN
            Authority ON School.auth_id = Authority.auth_id
        WHERE
            Authority.auth_id = #arguments.authorityID#
        AND
            Questions.question_id = #arguments.questionID#
        AND
             Responses.survey_id IN (SELECT Survey.survey_id FROM Survey where Survey.sch_id in (SELECT School.sch_id FROM School where auth_id=#arguments.authorityID#))
        ORDER BY
            responseYear ASC

How can I resolve?

Thanks

Strawberry · Accepted Answer · 2013-07-26 10:50:53Z

1

I think that this can be simplified as follows...

SELECT DISTINCT YEAR(res_created) responseYear
  FROM Responses r
  JOIN Questions q
    ON q.question_id = r.question_id 
  JOIN Lookup_Survey_Questions lsq
    ON lsq.question_id = q.question_id 
  JOIN Survey u
    ON u.survey_id = lsq.survey_id
  JOIN School c
    ON c.sch_id = u.sch_id
  JOIN Authority a
    ON a.auth_id = c.auth_id
 WHERE a.auth_id = #arguments.authorityID#
   AND q.question_id = #arguments.questionID#
   AND c.auth_id=#arguments.authorityID#
 ORDER 
    BY responseYear ASC
 LIMIT 2;

answered Jul 26, 2013 at 10:50

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joni · Accepted Answer · 2013-07-26 10:34:38Z

1

"Top 2" isn't MySQL syntax. To select the first two results add limit 2 to the end of the query:

...
    ORDER BY
        responseYear ASC LIMIT 2

answered Jul 26, 2013 at 10:34

Joni

112k14 gold badges151 silver badges201 bronze badges

Comments

Code Lღver · Accepted Answer · 2013-07-26 10:35:45Z

You are using the two clause at a place. You are using the DISTINCT and TOP that's why there is error.

Use this query:

SELECT DISTINCT YEAR(res_created) AS responseYear 
       FROM Responses 
           INNER JOIN Questions ON Responses.question_id = Questions.question_id 
           INNER JOIN Lookup_Survey_Questions ON Questions.question_id = Lookup_Survey_Questions.question_id 
           INNER JOIN Survey ON Lookup_Survey_Questions.survey_id = Survey.survey_id 
           INNER JOIN School ON Survey.sch_id = School.sch_id INNER JOIN Authority ON School.auth_id = Authority.auth_id 
           WHERE Authority.auth_id = 5 AND Questions.question_id = 20 AND Responses.survey_id IN (
            SELECT Survey.survey_id FROM Survey where Survey.sch_id in (SELECT School.sch_id FROM School where auth_id=5)) 
           ORDER BY responseYear ASC
           LIMIT 0,2; //use the limit

Collectives™ on Stack Overflow

MySql error in your SQL syntax for date

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related