combining string data of 3 tables into a single table procedure-CodePudding

I have three tables with similar table structure:

table_A(userId, month, year, some_text) table_B(userId, month, year, some_text) table_C(userId, month, year, some_text)

some_text is a column that can contain string values such as Alpha, Beta, Gamma

Requirement: I need to create a procedure that will combine (concat) some_text value of each (userId, month, year) into one.

Here's an example:

table_A(1, 'JAN', 2022, 'Alpha')
table_B(1, 'JAN', 2022, 'Beta')
table_C(1, 'JAN', 2022, 'Gamma')
table_C(2, 'JAN', 2022, 'Gamma')

Combining the above data into another table called table_X would look like this:

table_X(**userId, month, year**, some_text):
(1, 'JAN', 2022, '[Alpha, Beta, Gamma]')
(2, 'JAN', 2022, '[Gamma]')

As mentioned above, the first row in the target table contains concatenated values of some_text column from table_A, table_B and table_C.

The second row contains only "[Gamma]" since there was only one row given for that particular (userId, month, year).

How can I achieve the above requirement?

CodePudding user response：

you can use union all to combine 3 tables refer : https://www.techonthenet.com/mysql/union_all.php#:~:text=The MySQL UNION ALL operator is used to combine the,between the various SELECT statements.

syntax :

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION ALL
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

CodePudding user response：

The below query would work.

To create Table_X:

create table Table_X(userid INT AUTO_INCREMENT PRIMARY KEY,month varchar(5),year varchar(5),some_txt varchar(50));

To add data to it with above condition:

 insert into Table_X (select userid, month, year, GROUP_CONCAT (some_txt ) as some_txt from (select * from Table_A UNION ALL select from Table_B UNION ALL select * from Table_C) group by userid, month, year);