I will be very grateful for any help. My situation.. I have a table with students (I write only main columns for this question):
| Id | Lastname | Firstname |
_____________________________
| 1 | Smith | John |
| 2 | Williams | Robert |
| 3 | Wilson | David |
...
A table with works:
| Id | Name |
_________________
| 1 | Work1 |
| 2 | Work2 |
...
A table where write who did this work (number of students who can do the same work can be any)
| Id | WorkId | StudentId |
___________________________
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
...
And I need to make a table in procedure (with using cursor) like this:
| WorkName | Students |
_____________________________________________________
| Work1 | (Smith J., Williams R.) |
| Work2 | (Smith J., Williams R., Wilson D.) |
...
I don't understand completely how to use cursor in this case. My code:
DELIMITER //
CREATE PROCEDURE Report()
BEGIN
DECLARE students VARCHAR(200);
DECLARE id_s INT;
DECLARE done INT DEFAULT FALSE;
DECLARE get_cur CURSOR FOR
SELECT Id, CONCAT(Lastname, ' ', LEFT(Firstname,1), '. ') FROM students;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open get_cur;
read_loop: LOOP
FETCH get_cur INTO id_s, students;
IF done THEN
LEAVE read_loop;
END IF;
SELECT w.Name, students
FROM Work w
JOIN WorkStudent ws ON w.Id=ws.WorkId
JOIN Students s ON s.Id = ws.StudentId
WHERE s.Id=id_s;
END LOOP;
CLOSE get_cur;
END //
DELIMITER ;