5

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.

John Woo
  • 258,903
  • 69
  • 498
  • 492
user2442377
  • 71
  • 1
  • 7
  • possible duplicate of [Can I concatenate multiple MySQL rows into one field?](http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) – Thomas Jun 04 '13 at 03:18

2 Answers2

10

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)
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 1
    This is great, though it is important to observe the performance. +1 – KaeL Jun 04 '13 at 03:19
  • 2
    Thanks for the answer and the suggestions. The previous developer who worked on this little software messed up the table structures. But surely i would consider normalizing the tables. Thanks again. – user2442377 Jun 04 '13 at 04:18
1

I like the FIND_IN_SET option since you are using MySQL, but here is an alternative solution that works as well using LIKE in the 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 look into normalizing your data -- storing a comma delimited list in a relational database is usually a bad idea.

sgeddes
  • 62,311
  • 6
  • 61
  • 83