How do I join using a string with a comma separated value?

Question

I want to join 2 tables, one table having an email field and the other having a comma separated email list.

This is the scenario:

Tables

Team
--------------
- team_id
- email_list (this is a comma separated email address)


Persons
--------------
 - person_id
 - email

I tried something like this:

---

SELECT team.* FROM team INNER JOIN persons ON trim(persons.email) IN (CONCAT('\'',REPLACE(REPLACE(team.email_list,' ',''),',','\',\''),'\''))

but the string inside the IN clause seems to be like this "'email1','email2','email3'"

Any ideas to make it work?

Answer 1

MySQL has a built-in function that can help with comma-separated lists:

SELECT  . . .
FROM team t INNER JOIN persons p 
  ON FIND_IN_SET(p.email, t.email_list);

But you won't be happy with the performance, since it can't be optimized to use an index.

See also my answer to Is storing a comma separated list in a database column really that bad?