user2442377 user2442377 - 3 months ago 12
MySQL Question

mysql join two table with comma separated ids

I have two tables

Table 1

ID NAME
1 Person1
2 Person2
3 Person3


Table 2

ID GROUP_ID
1 1
2 2,3


The IDs in all the columns above refer to the same ID (Example - a Department)

My Expected output (by joining both the tables)

GROUP_ID NAME
1 Person1
2,3 Person2,Person3


Is there a query with which i can achieve this. Your help is highly appreciated. Thank you.

Answer

You can use FIND_IN_SET() and GROUP_CONCAT() on this,

SELECT  b.Group_ID, GROUP_CONCAT(a.name) name
FROM    Table2 b
        INNER JOIN Table1 a
            ON FIND_IN_SET(a.ID, b.Group_ID) > 0
GROUP   BY b.Group_ID

OUTPUT

╔══════════╦═════════════════╗
║ GROUP_ID ║      NAME       ║
╠══════════╬═════════════════╣
║ 1        ║ Person1         ║
║ 2,3      ║ Person2,Person3 ║
╚══════════╩═════════════════╝

As a sidenote, this query might not perform efficiently as expected. Please do normalize your table properly by not saving values separated by a comma.

UPDATE

GROUP_ID is pretty much confusing. Isn't it PersonIDList? Anyway, here's my suggested schema design:

PERSON Table

  • PersonID (PK)
  • PersonName
  • other columns..

GROUP Table

  • GroupID (PK)
  • GroupName
  • other columns..

PERSON_GROUP Table

  • PersonID (FK) (at the same time PK with column GroupID)
  • GroupID (FK)