CODEWITHSUNDEEP
mysql
Wesley Smith
Wesley Smith

Reputation: 19571

Can I join on Contains or like in mySQL?

Lets say I have a table called emails like this:

╔═════════════════════════╦════════════════════════════════════╗
║ subject                 ║ body                               ║
╠═════════════════════════╬════════════════════════════════════╣
║     Some Subject        ║ Some text .....Client ID: 13456    ║
║     Some Subject        ║ Some text .....Client ID: 13457    ║
║     Some Subject        ║ Some text .....Client ID: 13458    ║
║     Some Subject        ║ Some text .....Client ID: 13459    ║
╚═════════════════════════╩════════════════════════════════════╝

and a table named clients like this:

╔═══════════════╦══════════╗
║ client        ║ clientID ║
╠═══════════════╬══════════╣
║     Company 1 ║ 13456    ║
║     Company 2 ║ 13457    ║
║     Company 3 ║ 13458    ║
║     Company 4 ║ 13459    ║
╚═══════════════╩══════════╝

How can I join the two where the body column of the first table contains the clientId from the second table and get the below result?:

╔═════════════════════════╦════════════════════════════════════╦══════════╦═════════╗
║ subject                 ║ body                               ║client    ║clientId ║
╠═════════════════════════╬════════════════════════════════════╬══════════╬═════════╣
║     Some Subject        ║ Some text .....Client ID: 13456    ║Company 1 ║13456    ║ 
║     Some Subject        ║ Some text .....Client ID: 13457    ║Company 2 ║13457    ║
║     Some Subject        ║ Some text .....Client ID: 13458    ║Company 3 ║13458    ║
║     Some Subject        ║ Some text .....Client ID: 13459    ║Company 4 ║13459    ║
╚═════════════════════════╩════════════════════════════════════╩══════════╩═════════╝

I tried this: 

select * 
from emails e
left join clients c 
on e.body like  c.clientId
where e.subject =  'Some Subject'

Unfortunately running the above justs spins for over 5 minutes before I aborted the query which would seem to indicate that it is not correct or is extremely inefficient.

Is it possible to build a query that will do this? possibly by using contains?

All, thanks for your answers, I'm off till monday and will come back to this when I next go into work.

Upvotes: 1

Views: 52

Answers (4)

Vipin Jain
Vipin Jain

Reputation: 3756

We can get clientid from body using substring_index() function

select * from emails e 
left join clients c 
on c.clientid = substring_index(r.body,':', 1) 
where e.subject = 'Some Subject'

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can create a new Column in the the email that will automatic calculate the clientID from the body field. So it will only do it one time when you set the body.

try this:

ALTER TABLE email
  ADD COLUMN clientId INT AS (SUBSTRING_INDEX(body,' ',-1)) PERSISTENT,
  ADD KEY (`clientId`) ;

Then you can use the new field, with a index in your SELECT.

select * 
  from emails e
  left join clients c 
  on e.clientId like  c.clientId
  where e.subject =  'Some Subject';

Please let me know if it works for you.

Upvotes: 1

Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ
Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ

Reputation: 4216

Concatenate '%' after and before your clientID. (If you are sure that clientID will be always the last text in the string, you don't need it after).

select * 
from emails e
left join clients c 
on e.body like  CONCAT('%', c.clientId, '%')
where e.subject =  'Some Subject'

Upvotes: 1

zedfoxus
zedfoxus

Reputation: 37109

Can you give this a shot?

select *
from emails e
left join clients c 
  on e.body regexp concat(': ', c.clientid, '$') = 1

Compare the above with:

select *
from emails e
left join clients c on e.body like concat('%: ', c.clientid);

Example: http://sqlfiddle.com/#!9/c2161/5

create table emails (subject varchar(100), body text);
insert into emails values ('test', 'testing: 100'), ('test2', 'testing2: 200');

create table clients (client varchar(100), clientid int);
insert into clients values ('company 1', 100), ('company 2', 200);

Results:

| subject |          body |    client | clientid |
|---------|---------------|-----------|----------|
|    test |  testing: 100 | company 1 |      100 |
|   test2 | testing2: 200 | company 2 |      200 |

Upvotes: 1

Related Questions