title: Mysql joining two tables where part of string in table1 are in table2
body: <p>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.</p>
  
  <p>This is my tables structure :</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This is what I have tried so far,</p>
  
  <pre><code>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),'%')
  </code></pre>
  
  <p>This return only <code>ardiansyah, setiawan thea and rusdy</code> instead of all employees except jonathan. Please note that I use <code>inner join</code> rather than <code>left join</code>, because I want to eliminated employes that <code>not in ms_absen</code> table.</p>