CODEWITHSUNDEEP
mysqljoindenormalization
thedjaney
thedjaney

Reputation: 1126

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

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?

Upvotes: 5

Views: 1532

Answers (2)

Coenie Richards
Coenie Richards

Reputation: 568

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,'[email protected],[email protected]' union all
SELECT 2,'[email protected],[email protected]' union all
SELECT 3,'[email protected],[email protected]' union all
SELECT 4,'[email protected],[email protected],[email protected]' UNION ALL
SELECT 3,'[email protected],[email protected]' 
;


insert into @persons
select 1,'[email protected]' union all
select 2,'[email protected]' union all
select 3,'[email protected]';

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

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562260

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?

Upvotes: 8

Related Questions