MySQL Trigger Nested Group_Concat Duplicates

Question

I have a trigger to conditionally GROUP_CONCAT a few columns. I can remove duplicates from within a single column, but I’m struggling to remove duplicates between two or more columns.

Current trigger (two column example):

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;

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

can you please explain in more words what you want to get, the sample really doesn't clear it up — nbk
– nbk, Commented Jul 10, 2021 at 21:17
@nbk. I want to join the two columns into one row without duplicates. In my fiddle it will remove duplicates for a single column, stocks1 or stocks2, but not between stocks1 and stock2. If stocks1 and stocks2 both had ‘BRK’, I would only want to see it once in the results, not twice. — aimee
– aimee, Commented Jul 10, 2021 at 21:29

nbk · Accepted Answer · 2021-07-11 17:24:31Z

1

Change the second Select to

-- 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     | GOOG   
INTC    | NKE    
TSLA    | APPL   
APPL    | NKE    
TSLA    | FB     
NKE     | BRK

CREATE TRIGGER trigger1
BEFORE UPDATE
ON Table1
FOR EACH ROW
BEGIN
set NEW.results =
(SELECT CONCAT('<br>',GROUP_CONCAT( val
                                SEPARATOR '<br>'))
FROM (SELECT stocks1 as val FROM                    
                        Table2
                    WHERE
                        NEW.selection2 = 'x'
    UNION
       SELECT stocks2 FROM                    
                        Table2
                    WHERE
                        NEW.selection2 = 'x') t1
);
end;

update Table1
set selection1 = 'x', selection2 = 'x'
where id = 1

select * from Table1

id | selection1 | selection2 | results                                             
-: | :--------- | :--------- | :---------------------------------------------------
 1 | x          | x          | <br>BRK<br>INTC<br>TSLA<br>APPL<br>NKE<br>GOOG<br>FB
 2 | null       | null       | null                                                
 3 | null       | null       | null

db<>fiddle here

edited Jul 11, 2021 at 17:24

answered Jul 10, 2021 at 21:45

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

aimee Over a year ago

That works. If there were more columns I guess I just have to keep adding comparisons?

nbk Over a year ago

yeah i thought about more columns, and would write a IN (SELECT stocks1...UNION SELECT stocks2 ....) for all other columns that should keep the code simple

aimee Over a year ago

Do you have an example of what that would look like?

nbk Over a year ago

sure dbfiddle.uk/…

aimee Over a year ago

That’s great! Thanks!

|

Collectives™ on Stack Overflow

MySQL Trigger Nested Group_Concat Duplicates

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related