php - how to match with like in other table and count

Question

i have 2 tables areas2013

row_id | area              |teamleader
1      |  1234-Asia        |   Joe 
2      |  12345-Europe     |   Juan  
3      | 123456-UK         |   Ple 

and f12

row_id| eacode
1     | 1234
2     | 12345
3     | 1234

as you can see i want to match the eacode and area using like% and count how many eacode.

i want something like this in php

eacode| area | count | teamleader 
1234  | Asia  |   2    |  Joe
12345 | Europe|   1    |  Juan

sorry for my bad english

Answer 1

SELECT eacode, substr(area, locate('-', area) + 1) area, count(*) `count`, teamleader
FROM f12
JOIN areas2013 ON area like CONCAT(eacode, '-%')
GROUP BY eacode