[21000][1242] Subquery returns more than 1 row

My Query:

entityManager.createQuery("SELECT " +
                "q.id, " +
                "q.title, " +
                "q.user.fullName, " +
                "q.user.reputationCount, " +
                "q.viewCount, " +
                "q.countValuable, " +
                "q.persistDateTime,  " +
                "t.id, " +
                "t.name, " +
                "t.description, " +
                "(SELECT COUNT (a) FROM Answer a WHERE a.question.id = q.id), " +
                "(SELECT a.isHelpful FROM Answer a WHERE a.question.id = q.id) " +
                "FROM Question q JOIN q.tags t")

Here I get the error – [21000][1242] Subquery returns more than 1 row

By the method of exceptions, I determined that the error in this query string:

"(SELECT a.isHelpful FROM Answer a WHERE a.question.id = q.id) "

How to make the correct request so that there is no this error? Thank!

Answer

Two common ways are aggregation and limiting:

(SELECT MAX(a.isHelpful) FROM Answer a WHERE a.question.id = q.id)
(SELECT a.isHelpful FROM Answer a WHERE a.question.id = q.id LIMIT 1)

However, those are really just hacks to get around an “issue” with the data. I put issue in quotes, but the real issue is probably your understanding of data and not the data itself.

You should understand why there are duplicates. Then decide which value you want. And implement the correct logic for what you want.