By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Table1
CREATE TABLE Table1 (id INT,selection1 varchar(50),selection2 varchar(50),results varchar(1000));
INSERT INTO Table1 (id,selection1,selection2,results)
VALUES
(1,null,null,null),
(2,null,null,null),
(3,null,null,null);
select * from Table1
| id | selection1 | selection2 | results |
|---|---|---|---|
| 1 | null | null | null |
| 2 | null | null | null |
| 3 | null | null | null |
-- Table2
CREATE TABLE Table2 (stocks1 VARCHAR(50),stocks2 VARCHAR(50));
INSERT INTO Table2 (stocks1,stocks2)
VALUES
('BRK','BRK'),
('BRK','BRK');
-- ('BRK','GOOG'),
-- ('INTC','NKE'),
-- ('TSLA','APPL'),
-- ('APPL','NKE'),
-- ('TSLA','FB'),
-- ('NKE','BRK');
select * from Table2
| stocks1 | stocks2 |
|---|---|
| BRK | BRK |
| BRK | BRK |
CREATE TRIGGER trigger1
BEFORE UPDATE
ON Table1
FOR EACH ROW
BEGIN
set NEW.results =
(SELECT (GROUP_CONCAT(distinct (CONCAT_WS('<br>',
(SELECT (GROUP_CONCAT(distinct stocks1 SEPARATOR '<br>'))
FROM Table2
where NEW.selection1 = 'x'),
(SELECT (GROUP_CONCAT(distinct stocks2 SEPARATOR '<br>'))
FROM Table2
where NEW.selection2 = 'x')
)))));
end;
update Table1
set selection1 = 'x', selection2 = 'x'
where id = 1
select * from Table1
| id | selection1 | selection2 | results |
|---|---|---|---|
| 1 | x | x | BRK<br>BRK |
| 2 | null | null | null |
| 3 | null | null | null |