Mysql joins two tables with comma separated identifiers

Question

I have two tables

Table 1

ID NAME 1 Person1 2 Person2 3 Person3 

table 2

 ID GROUP_ID 1 1 2 2,3 

Identifiers in all columns above refer to one identifier (example - department)

My expected result (by joining both tables)

 GROUP_ID NAME 1 Person1 2,3 Person2,Person3 

Is there a request with which I can do this. Your help is much appreciated. Thanks.

Answer 1

You can use FIND_IN_SET() and GROUP_CONCAT() ,

 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 

• SQLFiddle Demo
• MySQL FIND_IN_SET
• MySQL GROUP_CONCAT ()

OUTPUT

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

As a side element, this request may not work as efficiently as expected. Correctly normalize the table without saving comma-separated values.

UPDATE

GROUP_ID pretty confusing. Isn't that a PersonIDList ? Anyway, here is my suggested circuit design:

PERSON table

• PersonID (PK)
• Personname
• other columns.

GROUP table

• GroupID (PK)
• Groupname
• other columns.

Table PERSON_GROUP

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

Answer 2

I like the FIND_IN_SET option since you are using MySQL, but here is an alternative solution that also works with LIKE in JOIN :

 select t2.group_id, group_concat(t1.name order by t1.name separator ',' ) name from t1 inner join t2 on concat(',',t2.group_id,',') like concat('%,',t1.id,',%') group by t2.group_id; 

SQL Fiddle Demo

I would suggest you study the normalization of your data. Saving a comma-delimited list in a relational database is usually bad.