3

I'm having a problem in MySQL query.
I need MySQL query to display the following output and I also need to take CSV or Excel or PDF report.

Table1:

id | nos
-------------
1    12,13,14
2    14
3    14,12

Table2:

id | values
------------
12   raja
13   rames
14   ravi

I want output like this:

id | values
---------------------
1    raja, rames, ravi
2    ravi
3    ravi, raja
Adam Rackis
  • 82,527
  • 56
  • 270
  • 393
raja
  • 35
  • 2
  • 6

1 Answers1

5

In SQL, it's better to store a single value in a column, not a comma-separated list of values. See my answer to Is storing a comma separated list in a database column really that bad?

You can try this query, but it will be terribly slow and inefficient:

SELECT Table1.id, GROUP_CONCAT(Table2.values) AS values
FROM Table1
JOIN Table2 ON FIND_IN_SET(Table2.id, Table1.nos)
GROUP BY Table1.id;

See the FIND_IN_SET() function.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • thanks Bill Karwin for your valuable answer.. from this i got a idea to work in mysql... – raja Nov 10 '11 at 10:50
  • hi Bill Karwin but the output seem to be change in order id | values --------------------- 1 ravi, rames, raja 2 ravi 3 ravi, raja – raja Nov 10 '11 at 11:10
  • Look up the documentation on [GROUP_CONCAT()](http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat). You can specify the sort order. – Bill Karwin Nov 10 '11 at 17:56