CODEWITHSUNDEEP
mysqlstored-procedures
Mike Feng
Mike Feng

Reputation: 833

MySQL Stored Procedure to loop through 2 tables, select values, and store in 3rd

I have 3 tables users, memos, and memos_read.

users:

+---+----------+
|ID | NAME     |
+---+----------+
| 1 | Mike     |
| 4 | Susan    |
| 5 | Peter    |
| 6 | Mary     |
+---+----------+

memos:

+---+----------+
|ID | CONTENT  |
+---+----------+
| 15| Hello    |
| 16| World    |
| 17| Foo      |
| 23| Bar      |
+---+----------+

I want to create a stored procedure with a nested loop to create a "read map" for memos that have been read, creating something like:

memos_read:

+---------+----------+
| MEMO_ID | USER_ID  |
+---------+----------+
|    15   |    1     |
|    15   |    4     |
|    15   |    5     |
|    15   |    6     |
|    16   |    1     |
|    16   |    4     |
|    16   |    5     |
|    16   |    6     |
|    17   |    1     |
|    17   |    4     |
|    17   |    5     |
|    17   |    6     |
|    23   |    1     |
|    23   |    4     |
|    23   |    5     |
|    23   |    6     |
+---------+----------+

Is this possible? Thank you in advanced!

Upvotes: 1

Views: 406

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

@Nick's answer should work for you; however if you are not interested in creating a new table, and rather use the "read map" dynamically inside a query; you can utilize a Derived Table.

General query structure would be something as follows:

SELECT
  read_map.MEMO_ID,
  read_map.USER_ID,
  ...
FROM 
(
  SELECT 
    m.ID AS MEMO_ID, 
    u.ID AS USER_ID
  FROM users AS u 
  CROSS JOIN memos AS m 
) AS read_map 

...  -- here you can join with other tables as required
WHERE ...

Upvotes: 1

Nick
Nick

Reputation: 147166

You can use CREATE TABLE ... SELECT syntax to create the memos_read table:

CREATE TABLE memos_read AS
SELECT m.ID AS MEMO_ID, u.ID AS USER_ID
FROM memos m 
CROSS JOIN users u

Output is as you requested. Demo on dbfiddle

Upvotes: 1

Related Questions