Select Join From 2 table wich one of the table has array value of key from another table

Question

I have 2 table :

Table A

|id  | Name   |
--------------
|P001| Samsung|
|P002| Nokia  |
|P003| Oppo  |

Table B

|id   |      Value       |
--------------------------
|1    | P001,P002,P003  |

I need a query to generate the view below

|id   |          Value       |
------------------------------
|1    | Samsung,Nokia,Oppo  |

This is going to be (almost) impossible to do on SQL Server, and very ugly even on MySQL 8+. You should avoid storing CSV data like this. — Tim Biegeleisen, Mar 01 '21 at 08:01
Does the values in CSV (table B) may really contain a space after a comma? *I have 2 table* Provide them as precise CREATE TABLE + INSERT INTO scripts. And specify **precise** MySQL version. — Akina, Mar 01 '21 at 08:09
I see a space between comma and P003 in source and between comma and Oppo in output. If "no space" then edit your sample in the question text. — Akina, Mar 01 '21 at 08:47
@Akina that not main problem in this post, the problem is , how to make query to get the result. :) — haris j, Mar 01 '21 at 09:14
*that not main problem in this post* This **is critical**. If your CSV does **not** contain these parasite spaces then simple method (FIND_IN_SET) exists, but it is not applicable when spaces exists. — Akina, Mar 01 '21 at 09:16
@Akina Oke so assumed : 1. contain space after comma what the query ? , 2.And assumed thera are not space after comma than what the query ? — haris j, Mar 01 '21 at 09:32

Akina · Accepted Answer · 2021-03-01T09:44:47.777

1

2.And assumed thera are not space after comma than what the query ?

SELECT b.id, GROUP_CONCAT(a.name) value
FROM a
JOIN b ON FIND_IN_SET(a.id, b.value)
GROUP BY b.id;

fiddle (with demonstration that the space after a comma presence result in incorrect output, and possible correction).

Of course, a.id must not contain commas and/or spaces.

Also you may add ORDER BY FIND_IN_SET(a.id, b.value) clause to GROUP_CONCAT() if the order of translated names is important.

edited Mar 01 '21 at 09:44

answered Mar 01 '21 at 09:39

Akina

1 Answers1