MySQL said: Documentation- Subquery returns more than 1 row

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?

Answer

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.