A list with correlated countries

Question

I have two tables The first one is similar to:

    | id | id_related     | 
    | id1| [id2, id3, id4]|
    | id2| [id1, id4]     |
    | id3| [id1, id2, id5]|

The second is similar to:

    |id | country |
    |id1| BR      |
    |id2| US      |
    |id3| CO      |
    |id4| IT      |
    |id5| US      |

I need a query that return:


   | id | id_related     | related_country |
   | id1| [id2, id3, id4]| [US, CO, IT]    |
   | id2| [id1, id4]     | [BR, IT]        |
   | id3| [id1, id2, id5]| [BR, US, US]    |

I trying to do a select with a select and concat, but nothing is working. Can someone help me?

You could try parsing the list of IDs as JSON, resolving them to countries, then GROUP_CONCAT them together. — Seva Alekseyev, May 24 '22 at 01:32
i am trying it: SELECT rt.ids, rt.related_ids, GROUP_CONCAT(country) as countries FROM related_table as rt left join all_countries as ac ON rt.ids = ac.ids WHERE rt.ids IN (SELECT rt.related_ids FROM rt) but appears this error: This query ran against the rt database, unless qualified by the query. however, if do not use group concat it works (returned the countries in different rows) — lidiaxp, May 24 '22 at 01:41
Please provide your tables as CREATE TABLE + INSERT INTO scripts. Also specify the datatype (string? json?) of desired output columns and exact MySQL version. — Akina, May 24 '22 at 05:33
Honestly speaking, using the **SET** data type to simulate an array is not recommended. It's diffcult to manage the contents and maintain the data consistency. Why not make a foreign key column instead of the **SET** in the first table and have it references the second table? The FK constraint can warrant the data integrity. — blabla_bingo, May 24 '22 at 08:23

RF1991 · Accepted Answer · 2022-05-24T12:24:00.107

your data

CREATE TABLE mytable1(
   id         VARCHAR(100) NOT NULL 
  ,id_related VARCHAR(100) NOT NULL

);
INSERT INTO mytable1
(id,id_related) VALUES 
('id1','[id2,id3,id4]'),
('id2','[id1,id4]'),
('id3','[id1,id2,id5]');

CREATE TABLE mytable2(
   id      VARCHAR(100) NOT NULL 
  ,country VARCHAR(100) NOT NULL
);
INSERT INTO mytable2(id,country) VALUES 
('id1','BR'),  
('id2','US'),
('id3','CO'),
('id4','IT'),
('id5','US');

first of all you should remove [ ] characters by using following query

SELECT id,
       Trim(BOTH '[' FROM Trim(BOTH ']' FROM id_related)) AS id_related
FROM   table1

output result

id	id_related
id1	id2,id3,id4
id2	id1,id4
id3	id1,id2,id5

then you should use the approach used in this link to split values to multiple rows

SELECT t12.id,
       t2.country
FROM   (SELECT t11.id,
               Substring_index(Substring_index(t11.id_related, ',', numbers.n),
               ',', -1
                      )
               id_related
        FROM   (SELECT 1 n
                UNION ALL
                SELECT 2
                UNION ALL
                SELECT 3
                UNION ALL
                SELECT 4
                UNION ALL
                SELECT 5) numbers
               INNER JOIN (SELECT id,
                                  Trim(BOTH '[' FROM Trim(BOTH ']' FROM
                                                     id_related)) AS
                                                     id_related
                           FROM   table1) t11
                       ON Char_length(t11.id_related) - Char_length(
                          REPLACE(t11.id_related, ','
                          , '')) >=
                          numbers.n - 1) t12
       JOIN table2 t2
         ON t2.id = t12.id_related

to get following result

id	id_related
id3	id1
id2	id1
id1	id2
id3	id2
id2	id4
id1	id3
id3	id5
id1	id4

then use GROUP_CONCAT,Concat and multiple join and Subquery to get your desired result as follows

SELECT t12.id,
       t13.id_related,
       Concat('[', Group_concat(t2.country ORDER BY t12.id_related ASC), ']') AS
       related_country
FROM   (SELECT t11.id,
               Substring_index(Substring_index(t11.id_related, ',', numbers.n),
               ',', -1
                      )
               id_related
        FROM   (SELECT 1 n
                UNION ALL
                SELECT 2
                UNION ALL
                SELECT 3
                UNION ALL
                SELECT 4
                UNION ALL
                SELECT 5) numbers
               INNER JOIN (SELECT id,
                                  Trim(BOTH '[' FROM Trim(BOTH ']' FROM
                                                     id_related)) AS
                                                     id_related
                           FROM   table1) t11
                       ON Char_length(t11.id_related) - Char_length(
                          REPLACE(t11.id_related, ','
                          , '')) >=
                          numbers.n - 1) t12
       JOIN table2 t2
         ON t2.id = t12.id_related
       JOIN table1 t13
         ON t13.id = t12.id
GROUP  BY t12.id,
          t13.id_related

dbfiddle

A list with correlated countries

1 Answers1