1

Suppose firstly my table1 is:

id name
----------
1  abc
2  bcd
3  cde

In table2 i store

id table1_id
------------
1  1,2
2  1,3
3  1,2,3

I want output like this

id table1_value
----------------
1  abc,bcd
2  abc,cde
3  abc,bcd,cde

Please help me in with mysql query..

Tell Me How
  • 672
  • 12
  • 16
  • 3
    You can now see the benefits of Normalization and cons of storing CSV values in RDBMS. You can find hacky solutions(will be utterly slow with a decent data size) for this problem, but the real problem is your table structure. – Gurwinder Singh Mar 15 '17 at 05:44
  • See [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/a/3653574/20860) – Bill Karwin Mar 15 '17 at 06:14

1 Answers1

3

Below is a query which should meet your requirements. As @GurV commented above, you should avoid storing CSV data in your table, which is not normalized, and which thwarts much of the power which MySQL has as a relational database.

SELECT t2.id,
       GROUP_CONCAT(t1.name ORDER BY t1.name) AS table1_value
FROM table2 t2
INNER JOIN table1 t1
    ON FIND_IN_SET(t1.id, t2.table1_id) > 0
GROUP BY t2.id

Output:

enter image description here

Demo here:

Rextester

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360