0

I have a column in table A that takes the following values.

"1,2,3", "1,2", "2,1", "3,2"

I have another table B that has the following records.

("1","one"),("2","two"),("1","three")

I would like to get the following.

"one,two,three", "one,two", "two,one", "three,two"

How do I do this in MySQL?

I have tried the following.

select 
    A.comma_separated_numbers,
    group_concat(distinct B.word separator ',') as comma_separated_words
from A inner B 
on find_in_set(B.number, A.comma_separated_numbers)>0 
;
teha921
  • 11
  • 3
  • you should change that structure, by making another table where 1,2,3 values from the first table are stored in separate rows. this way it can be easily mapped using standard JOINs. this is how relational tables work – Your Common Sense Jan 20 '23 at 14:28
  • Thats table B we already have that. – teha921 Jan 20 '23 at 14:29
  • Please don't close the question the other question is irrelevant. – teha921 Jan 20 '23 at 14:29
  • The question is how to get "one,two,three", "one,two", "two,one", "three,two" from A and B. B being the table you are asking me to create. – teha921 Jan 20 '23 at 14:31
  • Negative. Table B is NOT that. You need a third table, C, where such numbers are stored in rows, not columns – Your Common Sense Jan 20 '23 at 14:32
  • Then you could use joins to get the mapping. Please read the linked answer, it explains these matters better – Your Common Sense Jan 20 '23 at 14:34
  • B has numbers stored in diffrent rows. Each entry enclosed in parenthesis is a row. – teha921 Jan 20 '23 at 14:36
  • I think an answer with a query instead words would benefit us all. – teha921 Jan 20 '23 at 14:38
  • Comma separated numbers from table A must be stored in table C, each on its row, linked to table A. this is how relational databases work – Your Common Sense Jan 20 '23 at 14:54
  • This C you are talking about is as useful as A and therefore meaningless to create. (It only has information thats already in A. Linking to A increase no information.) – teha921 Jan 20 '23 at 14:58
  • @YourCommonSense just for the record I was able to do it with the following query I did not read the other quesion (excessive information is just as bad as insufficient information) ``` select a.col1, group_concat(distinct b.col2 separator ',') from batch_dev.A as a left join batch_dev.B as b on find_in_set(b.col1, a.col1) group by a.col1; ``` – teha921 Jan 23 '23 at 02:37
  • @YourCommonSense The above solution does not use the extra table C you are talking about. – teha921 Jan 26 '23 at 00:27

0 Answers0