How I can merge multiplicate queries which return multiple columns in a procedure?

Question

I've 2 tables calls patients and events

---------         ----------
| users |         | events |
---------         ----------
|user_id|         |event_id|
|team_id|  1 -n   |user_id | 
|  .... |         | ....   | 
---------         ----------

I want to create a procedure to return the user name and their event's name

CREATE PROCEDURE getAll(user_id INT)
BEGIN
  DECLARE id_user INT;
  DECLARE done INT DEFAULT FALSE;
  DECLARE user CURSOR FOR SELECT user_id FROM users WHERE team_id = id_team;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN usuarios;
  update_loop: LOOP
    FETCH user INTO id_patient;

    IF done THEN
        LEAVE update_loop;
    END IF;

    SELECT name FROM users WHERE user_id = id_user;
    SELECT nameE FROM events WHERE user_id = id_user LIMIT 0,2;
  END LOOP;
END

I want to list the name and next, the name of their event to send a JSON to back. How can i do ? Temporary table?

Answer 1

You don't need use a cursor,try this:

DELIMITER $$

DROP PROCEDURE IF EXISTS getAll $$
CREATE PROCEDURE getAll(user_id INT)
BEGIN


SELECT u.user_id,u.name,e.ename FROM users u
INNER JOIN events e ON e.user_id = u.id_user
WHERE u.id_user = user_id ;

 END $$

DELIMITER ;

Solution Events grouped by user:

DELIMITER $$

    DROP PROCEDURE IF EXISTS getAll $$
    CREATE PROCEDURE getAll(user_id INT)
    BEGIN


    SELECT user_id,u.name,GROUP_CONCAT(e.ename SEPARATOR ',')  FROM users u
    INNER JOIN events e ON e.user_id = u.id_user
    WHERE u.id_user = user_id 
    GROUP BY u.id_user;

     END $$

    DELIMITER ;