MySQL said: Documentation- Subquery returns more than 1 row

Question

The following renders the correct output.

(
SELECT
   answer_data 
FROM
   wp_learndash_pro_quiz_statistic_ref b, wp_learndash_pro_quiz_statistic bb 
WHERE
   wp_users.id = b.user_id 
   AND b.statistic_ref_id = bb.statistic_ref_id 
   AND bb.question_id = 82
   AND b.quiz_id = 9) AS "xyz"

But, when I change bb.question_id = 82 to any other question_id (i.e. 56 is another question) I get this error:

" MySQL said: Documentation #1242 - Subquery returns more than 1 row"

Any idea what I'm doing wrong?

Execute it as single query (remove brackets and final alias) and count rows amount. — Akina, Dec 11 '20 at 04:55
Can you share the complete query? Seems like you are using result of the above query in another one. — Ankit Jindal, Dec 11 '20 at 04:55
Did you try doing `limit 1` at the end if you're only looking to receive a single row from the subquery? — singh.rbir, Dec 11 '20 at 04:56
The error message is clear and is telling you that your subquery, which needs to return a single scalar value, is returning more than one row. Either the logic is wrong, or it needs to be changed to return just a single value, possibly using `LIMIT`. — Tim Biegeleisen, Dec 11 '20 at 05:05
Does this answer your question? [subquery returns more than 1 row](https://stackoverflow.com/questions/14841945/subquery-returns-more-than-1-row) — But those new buttons though.., Dec 11 '20 at 05:27

score 0 · Accepted Answer · answered Dec 11 '20 at 05:07

It simply means that there are multiple records available for bb.question_id = 82 and you must have used it in the SELECT clause or with = operator in the WHERE clause where a single value is expected.

You can either use the aggregate function on answer_data
Or You can use the LIMIT 1 to fetch only one record out of multiple records

Use this:

(SELECT MAX(answer_data) 
  FROM wp_learndash_pro_quiz_statistic_ref b 
  JOIN wp_learndash_pro_quiz_statistic bb 
    ON wp_users.id = b.user_id 
   AND b.statistic_ref_id = bb.statistic_ref_id 
 WHERE bb.question_id = 82
   AND b.quiz_id = 9) AS "xyz"

Tip of the day: Always use the standard ANSI joins.

MySQL said: Documentation- Subquery returns more than 1 row

1 Answers1