CODEWITHSUNDEEP
mysqlsql
Raja Dhasan
Raja Dhasan

Reputation: 593

Mysql comma separated value

I have two tables which looks similar to below table 1 & Table 2.I want out put similar to table 3 

  Table 1

ParticipantsId | Description | Duration
1,2                 Demo        60 mins
1                   Test        25 mins
1,2,3,4             Alpha       30 mins
4,5,6,2             MCQ         120 mins

Table 2

UserId |  Name   |  Age
  1       Aku        21
  2       Greg       18
  3       Denver     24
  4       Mike       22
  5       Sid        24
  6       Sriten     19

I want the out put to be

Table 3 

users                   | Description | Duration
Aku,Greg                   Demo          60
Aku                        Test          25
Aku,Greg,Denver,Mike       Alpha         30
Mike,Sid,Sriten,Greg       MCQ           120

I have tried find_by_set , group and various other possibilities but I am not able to get the required results, Please help

Upvotes: 3

Views: 82

Answers (1)

mitkosoft
mitkosoft

Reputation: 5316

You can use GROUP_CONCAT() and FIND_IN_SET() together:

SELECT
    GROUP_CONCAT(t2.`name`) AS users,
    t1.Description,
    CAST(t1.Duration AS UNSIGNED) AS Duration
FROM
    table1 t1,
    table2 t2
WHERE
    FIND_IN_SET(t2.userID, t1.ParticipantsId)
GROUP BY
    t1.Description,
    CAST(t1.Duration AS UNSIGNED)

result is:

+----------------------+-------------+----------+
| users                | Description | Duration |
+----------------------+-------------+----------+
| Greg,Denver,Aku,Mike | Alpha       |       30 |
| Greg,Aku             | Demo        |       60 |
| Greg,Mike,Sid,Sriten | MCQ         |      120 |
| Aku                  | Test        |       25 |
+----------------------+-------------+----------+
4 rows in set

However you must think about normalizing of data structure, comma separated values is a bad practice as someone mentions before.

Upvotes: 4

Related Questions