title: While Loop in MySQL to select data with join and subquery
body: <p>I am beginner in MYSQL and I want to use while loop to get the data from the table using a select statement and joining another table via subquery.</p>
  <p>Please help me guide to construct my code, Below is the code I've written but there's always an error</p>
  <pre><code>
  DECLARE cn INT DEFAULT 0;
  DECLARE tb INT DEFAULT 0;
  SET tb = (SELECT COUNT(DISTINCT(exam_id)) FROM mais_writtenworks_detail);
  
  WHILE cn &lt;= tb DO
      SELECT ms.`first_name`,ms.`middle_name`, ms.`last_name`, rr.ww_score
      FROM mais_students ms
      LEFT JOIN
          (SELECT mwd.ww_score, mwd.`student_id`
          FROM mais_writtenworks_detail mwd
          WHERE mwd.`exam_id`= cn
          ) AS rr ON (ms.`ID`= rr.`student_id`)
          
      SET cn = cn + 1;
  END WHILE;
  
  </code></pre>
  <p>Here is the table</p>
  <p><a href="https://i.stack.imgur.com/47m8T.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/47m8T.png" alt="enter image description here" /></a></p>
  <p>I tried this approach, but the exam_id is dynamic so I want to use loop</p>
  <p><a href="https://i.stack.imgur.com/KiRhB.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KiRhB.png" alt="enter image description here" /></a></p>
  <p>I need your help guys,</p>