CODEWITHSUNDEEP
mysql
sensualstares
sensualstares

Reputation: 51

MySQL Stored Procedure Loop

So I'm needing to pull 2 id fields from 2 different tables and insert them into a third table. This is for a permissions thing, so basically I need to get the admin table's id and loop that until all of the building table's ids are populated into the third table called building_user. This is to "start" the permissions table so I need every building ID to be matched up with every admin ID.

Here is what I'm trying and I can't quite figure it out, but I know I'm missing something.

CREATE PROCEDURE buildingUserAssignment()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE done2 INT DEFAULT 0;
  DECLARE a, b INT;

   DECLARE admin CURSOR
   FOR
   SELECT id FROM admin;
   DECLARE building CURSOR
   FOR
   SELECT id FROM building;

   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;


  OPEN admin;
  OPEN building;
  REPEAT
    FETCH admin into a;

    REPEAT
    FETCH building into b;
    INSERT INTO building_user VALUES (a,b);
    UNTIL done END REPEAT;

  UNTIL done END REPEAT;


  CLOSE admin;
  CLOSE building;
END;

I end up only getting one admin ID field (ID #2, even though there is a ID 1). Here is the sample databases:

admin:

SELECT id FROM admin;
1
2
3
5
6
7
9
11

building:

SELECT id FROM building;
0
1
3
6
7
8
10

Current End Result of invoking buildingUserAssignment:

SELECT * FROM building_user:
user_id building_id
2   0
2   1
2   2
2   3
2   4
2   4
3   4
5   4
6   4
7   4
9   4
11   4

So the building_user table should be something like this in the end, repeated of course for each admin id:

1 0
1 1
1 3
1 6
1 7
1 8
1 10

I'm sure its something stupid, but I think I've looked at it too long and now I'm stuck. I may not even be doing this the "best" way either, so I welcome any suggestions.

Upvotes: 1

Views: 99

Answers (2)

Nikhil Sharma
Nikhil Sharma

Reputation: 1

WHILE i < v_count DO
    SET @start_date = JSON_UNQUOTE(JSON_EXTRACT(v_current_item, '$.start_date'));
    SET @expiry_date = JSON_UNQUOTE(JSON_EXTRACT(v_current_item, '$.expiry_date'));
    SET @amount = JSON_UNQUOTE(JSON_EXTRACT(v_current_item, '$.amount'));
    SET @emp_id = JSON_UNQUOTE(JSON_EXTRACT(v_current_item, '$.emp_id'));
    SET @update_id = JSON_UNQUOTE(JSON_EXTRACT(v_current_item,'$.update_id'));
                    SET i := i + 1;

 END WHILE;

Upvotes: 0

John Woo
John Woo

Reputation: 263713

You don't need to use loop. There is a type of join called CROSS JOIN which produces a result set from number of rows from first table multiplied by the number of rows of the second table. And the resulting rows will be insert into INSERT INTO..SELECT syntax.

INSERT INTO building_user 
SELECT a.id, b.id
FROM admin a CROSS JOIN building_user b

Upvotes: 2

Related Questions