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

Question

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!

What part of the error do you not understand? It seems quite clear. The subquery returns multiple rows. — Gordon Linoff, May 12 '20 at 18:42
Query "SELECT a.isHelpful FROM Answer a WHERE a.question.id = q.id" should be modified to return single row. MIN,MAX can be used but you have to decide. Add additional filter based on your use case to return single row for the query. — VN'sCorner, May 12 '20 at 18:45

score 0 · Answer 1 · answered May 12 '20 at 18:59

Subquery returns more than 1 row, this simply means that your query is not returning a single row for the outer select statement to work.

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

you have to apply a set of conditions to filter out your data uniquely or use joins to combine your table Answer and Question and then filter data accordingly.

you can also group each row data in one column by GROUP_CONCAT Mysql function like this :

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

Although GROUP_CONCAT is not available in Mysql, for that you can also bind SQL function in hibernate as described in this post.

score 0 · Accepted Answer · answered May 12 '20 at 19:07

0

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.

answered May 12 '20 at 19:07

Gordon Linoff

1,242,037
58
646
786

Unfortunately, this code does not work for me correctly :( – AlexMartynets May 12 '20 at 19:16
The entity Answer has isHelpful field, I need to extract it from this answer that corresponds to the question, how would I get only the value that corresponds only to this question? – AlexMartynets May 12 '20 at 19:23
May I don't need to use subquery? – AlexMartynets May 12 '20 at 19:30
@AlexMartynets . . . Either of those two versions will fix the error in this question. Whether it does anything useful or not cannot be answered because *this* question is about a run-time error (SOLVED). Perhaps you have another question about what you actually want to accomplish. – Gordon Linoff May 12 '20 at 21:15

score 0 · Answer 3 · answered May 13 '20 at 20:31

After a day of various trial and error, I found the following solution, I hope someone will broaden their horizons and help in solving their problem:

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 CASE WHEN MAX (a.isHelpful) > 0 THEN true ELSE false END FROM Answer a WHERE a.question.id = q.id) " +
                "FROM Question q JOIN q.tags t")

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

3 Answers3