create delimited string from table1 depend table 2

Question

my table1 is :

T1

col1    col2
 A1     C1,C2
 A2     C3,C5,C6
 A3     C4
 A4     C2,C5

and so table 2:

T2

col1    col2        col3
 A1     C1          reaction
 A1     C2          accept
 A2     C5          reaction
 A2     C6          manager
 A4     C2          manager

how to result this?:

query result

col1      col2
 A1       reaction,accept
 A2       NULL,reaction,manager
 A3       NULL
 A4       manager,NULL

please help me?

How is this different than your previous question? http://stackoverflow.com/questions/16507239/join-comma-delimited-data-column — sgeddes, Jul 13 '13 at 12:21

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

3

Never, never, never store multiple values in one column.

Like you see now this will only give you headaches. Normalize your table T1. Then you can join normally.

It should look like this

col1    col2
 A1     C1
 A1     C2
 A2     C3
 A2     C5
 A2     C6
 A3     C4
 A4     C2
 A4     C5

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 13 '13 at 12:07

juergen d

201,996
37
293
362

1

+1, though I think one never is missing. ;) – Fabian Bigler Jul 13 '13 at 12:09
Yes. It's not atomic! – felipe.zkn Jul 13 '13 at 12:10
He has used `GROUP_CONCAT` directly as a data structure. :) – Himanshu Jul 13 '13 at 12:12
Null value very important – Mahdi Yousef Jul 14 '13 at 08:17
my means: i want exactly result query. – Mahdi Yousef Jul 14 '13 at 11:03

create delimited string from table1 depend table 2

T1

T2

query result

1 Answers1

Never, never, never store multiple values in one column.