mysql, avoid limit in subquery

Question

I am looking to use limit in a (non-correlated) subquery. I have the following relationships:

Assessment - this represents a test. One of the columns in this table is created teacher id
Question - this represents a question. This has a 1:n relationship with assessment (assessment has 1 or more questions)
Incorrect answer - this represents an incorrect answer, this has a 1:n relationship with question (question has 1 or more incorrect answers)

I am trying to select a range of assessments (joined with their questions/incorrect answers) for a given teacher for use with a RESTful webservice using pagination (i.e. select assessments 5-10 and their associated questions/answers for a given teacher). However, I have been running into issues. I first ran into the following error (WHICH I HAVE ALREADY FIXED)

java.sql.SQLSyntaxErrorException: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

To fix this, I have my select nest two selects (as referenced in many SO posts, such as this one: Get all employee salary from employee tables from 3rd highest salary in MYSQL)

The sql I am running (with jdbc named template) is:

SELECT * 
FROM test.ASSESSMENT A 
LEFT OUTER JOIN test.QUESTION Q ON A.ISBN = Q.ASSESSMENT_ISBN 
LEFT OUTER JOIN test.INCORRECT_ANSWER I ON I.QUESTION_SID = 
Q.QUESTION_SID 
WHERE A.ISBN IN ( SELECT * 
                  FROM ( SELECT ISBN 
                         FROM test.ASSESSMENT 
                         WHERE CREATED_TEACHER_ID = :CREATED_TEACHER_ID 
                         ORDER BY ISBN ASC 
                         LIMIT :LIMIT, :OFFSET
                       ) AS T
                )

The exception (from the bottom of the stack trace) is:

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 ':CREATED_TEACHER_ID ORDER BY ISBN ASC LIMIT :LIMIT, :OFFSET) AS T)

Please advise if you have any suggestions.

I think I solved this using a (inner) join instead of an in clause. If this works and people are interested, I can share the answer. — Connor Butch, Mar 03 '20 at 04:34
Please add your answer, so it will be helpful for others in future — Venkataraman R, Mar 03 '20 at 04:36
Last error message tells that you forget to replace parameter placeholders with their values. PS. Intermediate subselect `SELECT * FROM ( ... ) AS T` is excess. — Akina, Mar 03 '20 at 04:41

score 0 · Answer 1 · answered Mar 03 '20 at 13:10

Move it to the FROM clause:

FROM test.ASSESSMENT A JOIN
     (SELECT DISTINCT ISBN
      FROM (SELECT ISBN 
            FROM test.ASSESSMENT 
            WHERE CREATED_TEACHER_ID = :CREATED_TEACHER_ID 
            ORDER BY ISBN ASC 
            LIMIT :LIMIT, :OFFSET
           ) a
     ) a2
     ON a2.ISBN = a.ISBN
     LEFT OUTER JOIN
     test.QUESTION Q
     ON A.ISBN = Q.ASSESSMENT_ISBN LEFT OUTER JOIN
     test.INCORRECT_ANSWER I
     ON I.QUESTION_SID = Q.QUESTION_SID

mysql, avoid limit in subquery

1 Answers1