5

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?

Community
  • 1
  • 1
thedjaney
  • 1,126
  • 2
  • 10
  • 28

2 Answers2

8

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?

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for this! I figured that this is going to be bad for performance... but I have to do it temporarily.. – thedjaney Dec 10 '12 at 03:22
0

I have used this for MSSQL, personally I don't like it, but for that one report or data tasks it does the trick. 

declare @team table (team_id int,email_list varchar(8000));
declare @persons table (person_id int,email varchar(64));

insert into @team
SELECT 1,'bob@domain.com,jane@domain.com' union all
SELECT 2,'ben@domain.com,jane@domain.com' union all
SELECT 3,'ben@domain.com,james@domain.com' union all
SELECT 4,'bill@domain.com,alice@domain.com,peter@domain.com' UNION ALL
SELECT 3,'alben@domain.com,james@domain.com' 
;


insert into @persons
select 1,'james@domain.com' union all
select 2,'ben@domain.com' union all
select 3,'alice@domain.com';

select 
  Team.team_id,
  Team.email_list,
  Person.person_id, 
  Person.email 'matched_on'
from
  @team Team
  INNER JOIN @persons Person on ','+Team.email_list+',' LIKE '%,'+Person.email+',%';

Updated as per David findings

Coenie Richards
  • 568
  • 12
  • 28
  • 1
    This will erroneously match `bert@example.com` in a list containing `robert@example.com`. To fix it use `on ',' + Team.email_list + ',' like '%,' + Person.email + ',%'`. – David Harkness Feb 25 '14 at 22:31