MySQL Group Concat Multiple Select

I would like to create a VIEW that concats several select statements, each using conditional rand(), and I’m running into issues with GROUP BY.

I’ve read a number of Stack posts that suggest changing the sql_mode to “only_full_group_by” but that looks like it might just cause more problems.

Each ID has two rows, one for each session (will make more sense when you see the fiddle) which I think is creating the problem.

I removed my GROUP BY attempts from the fiddle so it’s cleaner.

Fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c8fa6b9f94d7524f7d669a39ace646fa

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

GROUP CONCAT uis a aggregation function and needs a GROUP By

Also you need to concat all values for the GROUP CA

-- Table1

CREATE TABLE Table1 (id INT,session VARCHAR(50), value1 VARCHAR(50),
value2 VARCHAR(50),value3 VARCHAR(50),status VARCHAR(50));

INSERT INTO Table1 (id,session,value1,value2,value3,status) 
VALUES
(1,1,1,2,3,'inactive'),
(1,2,1,2,3,'inactive'),
(2,1,1,3,4,'inactive'),
(2,2,2,3,4,'inactive'),
(3,1,2,3,2,'active'),
(3,2,2,3,4,'inactive');

select * from Table1
✓

✓

id | session | value1 | value2 | value3 | status  
-: | :------ | :----- | :----- | :----- | :-------
 1 | 1       | 1      | 2      | 3      | inactive
 1 | 2       | 1      | 2      | 3      | inactive
 2 | 1       | 1      | 3      | 4      | inactive
 2 | 2       | 2      | 3      | 4      | inactive
 3 | 1       | 2      | 3      | 2      | active  
 3 | 2       | 2      | 3      | 4      | inactive
-- Table2

CREATE TABLE Table2 (value VARCHAR(50),status VARCHAR(50), 
results varchar(50));

INSERT INTO Table2 (value,status,results) 
VALUES
(1,'active','BRK'),
(2,'inactive','GOOG'),
(2,'active','HOG'),
(3,'active','XOM'),
(1,'inactive','INTC'),
(4,'inactive','APPL'),
(3,'active','NKE'),
(2,'inactive','SBUX'),
(1,'inactive','BCE'),
(1,'inactive','S&P'),
(2,'active','FB'),
(5,'inactive','FSLY'),
(1,'inactive','AMZN'),
(2,'inactive','ARKK'),
(3,'inactive','AGEN'),
(4,'inactive','C'),
(3,'inactive','TSLA'),
(2,'active','MSFT');

select * from Table2
✓

✓

value | status   | results
:---- | :------- | :------
1     | active   | BRK    
2     | inactive | GOOG   
2     | active   | HOG    
3     | active   | XOM    
1     | inactive | INTC   
4     | inactive | APPL   
3     | active   | NKE    
2     | inactive | SBUX   
1     | inactive | BCE    
1     | inactive | S&P    
2     | active   | FB     
5     | inactive | FSLY   
1     | inactive | AMZN   
2     | inactive | ARKK   
3     | inactive | AGEN   
4     | inactive | C      
3     | inactive | TSLA   
2     | active   | MSFT   
-- Table3

CREATE TABLE Table3 (id INT, session VARCHAR(50), results1 VARCHAR(50),
results2 VARCHAR(50),results3 VARCHAR(50));

INSERT INTO Table3 (id, session, results1, results2,results3) 
VALUES
(1,1,null,null,null),
(1,2,null,null,null),
(2,1,null,null,null),
(2,2,null,null,null),
(3,1,null,null,null),
(3,2,null,null,null);

select * from Table3
✓

✓

id | session | results1 | results2 | results3
-: | :------ | :------- | :------- | :-------
 1 | 1       | null     | null     | null    
 1 | 2       | null     | null     | null    
 2 | 1       | null     | null     | null    
 2 | 2       | null     | null     | null    
 3 | 1       | null     | null     | null    
 3 | 2       | null     | null     | null    
-- View1

CREATE VIEW view1 AS

SELECT Table1.id,Table1.session, (group_concat(CONCAT('Test:',

(select results from Table2
where (Table1.status = Table2.status
and Table1.value1 = Table2.value)
order by RAND() limit 1),

(select results from Table2
where (Table1.status = Table2.status
and Table1.value2 = Table2.value)
order by RAND() limit 1),

(select results from Table2
where (Table1.status = Table2.status
and Table1.value3 = Table2.value)
order by RAND() limit 1))

SEPARATOR ',')) as concated_column

from Table1
GROUP BY Table1.id,Table1.session;
SELECT Table1.id,Table1.session, (group_concat(CONCAT('Test:',

(select results from Table2
where (Table1.status = Table2.status
and Table1.value1 = Table2.value)
order by RAND() limit 1),

(select results from Table2
where (Table1.status = Table2.status
and Table1.value2 = Table2.value)
order by RAND() limit 1),

(select results from Table2
where (Table1.status = Table2.status
and Table1.value3 = Table2.value)
order by RAND() limit 1))

SEPARATOR ',')) as concated_column

from Table1
GROUP BY Table1.id,Table1.session;
id | session | concated_column  
-: | :------ | :----------------
 1 | 1       | Test:S&PSBUXTSLA 
 1 | 2       | Test:S&PSBUXAGEN 
 2 | 1       | Test:S&PAGENAPPL 
 2 | 2       | Test:SBUXTSLAAPPL
 3 | 1       | Test:FBXOMMSFT   
 3 | 2       | Test:GOOGTSLAAPPL
select * from view1;
id | session | concated_column  
-: | :------ | :----------------
 1 | 1       | Test:BCEGOOGAGEN 
 1 | 2       | Test:AMZNARKKAGEN
 2 | 1       | Test:BCETSLAAPPL 
 2 | 2       | Test:GOOGTSLAC   
 3 | 1       | Test:FBXOMFB     
 3 | 2       | Test:ARKKAGENAPPL

db<>fiddle here


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x