CODEWITHSUNDEEP
mysqlsqljoin
Zacurned Ace
Zacurned Ace

Reputation: 95

Mysql joining two tables where part of string in table1 are in table2

I am currently developing an employee database website. Previously my client use ms. excel as database storage which has no structure. But they already have thousands of data. I have already imported data into temporary tables. The problem is, I had to adjust the excel data with database structure.

This is my tables structure :

ms_absen :

id      name
------------------------
11      vnice ardian
22      setiawan ecco
33      andytaman1
44      rusdytaman2

ms_employee :

id_emp    name          
---------------------------
01        ardiansyah       
02        setiawan thea    
03        arif andy syah   
04        jonathan         
05        f rusdy          

expected result :

id_emp    name               id
--------------------------------------
01        ardiansyah         11
02        setiawan thea      22
03        arif andy syah     33
05        rusdy              44

This is what I have tried so far,

select e.id_emp,e.name,a.id
from ms_employee as e
inner join ms_absen a
on a.name like concat('%',left(e.name,3),'%')

This return only ardiansyah, setiawan thea and rusdy instead of all employees except jonathan. Please note that I use inner join rather than left join, because I want to eliminated employes that not in ms_absen table.

Upvotes: 1

Views: 3959

Answers (2)

zmii
zmii

Reputation: 4277

Given we have data presented in the question we can write something like this:

  1. This will result in a table with 3 columns. Let's assume the resulting table will be called A.

    SELECT id, SUBSTRING_INDEX( 'name' , ' ', 1 ) AS a, SUBSTRING_INDEX(SUBSTRING_INDEX( 'name' , ' ', 2 ),' ',-1) AS b FROM ms_absen

  2. This is pseudo (i haven't tested it) code that shows the idea.

    select e.id_emp, e.name, a.id from ms_employee as e, A as a where e.name like concat('%',left(a.part1,3),'%') or e.name like concat('%',left(a.part2,3),'%')

Upvotes: 0

zmii
zmii

Reputation: 4277

You can't map one thing to another ( or join in your state ) without having a mapping ).

If I were you I would try to map one of the tables to other table with good data and then apply something like you've specified

select e.id_emp,e.name,a.id
from ms_employee as e
inner join ms_absen a
on a.name like concat('%',left(e.name,3),'%')

Question is - how to transform your data into good one?

You see, you have a spaces in this name column. And if you had no spaces, then it would be easier. So you map one table to some transition table with columns id, name_part1, name_part2, ..., name_partN, where N is the maximum number of words in name ( finding this N can be done manually ). Then you can apply logic alike this one (this is the comment to MySQL documentation on string built-in functions):

// Posted by [name withheld] on March 22 2006 8:02pm

// This will split an IP address ("a.b.c.d") into 4 respective octets:

SELECT
`ip` ,
SUBSTRING_INDEX( `ip` , '.', 1 ) AS a,
SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', 2 ),'.',-1) AS b,
SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', -2 ),'.',1) AS c,
SUBSTRING_INDEX( `ip` , '.', -1 ) AS d
FROM log_table

Thus you will have a table where every column nameX where X is inside range 1...N.

This is the best I can suggest you for now without using RDBMS )

Upvotes: 1

Related Questions