CODEWITHSUNDEEP
mysqlfull-text-searchleft-joininner-join
Mannie Singh
Mannie Singh

Reputation: 129

Join Two Tables by Full Text Search

This database is only used to merge two tables one which contains locations and the other latitude and longitude values. What i want to do is join the two tables by location so that the latitude and longitude values are added to table 1.

Table 1:

Title:                Location:                          Latitude:       Longitude
pizza shop            london, chelsea, el13 4hr          Null            Null
Phone Shop            Manchester - Derby                 Null            Null
Computer Repair       Birmingham (b70)                   Null            Null


Table 2:

Location             Latitude:          Longitude:
London               53.6658            0.25533
birmingham           54.3665            0.89336
manchester           66.3368            0.25836

Table 1's location column can in some cases contain data using commas dashes or more then one location hence my choice to go with full text to match what ever first match and display there longitude and latitude values.

End Result for table 1 should be:

Title:                Location:                          Latitude:       Longitude
pizza shop            London                             53.6658         0.25533
Phone Shop            manchester                         66.3368         0.25836
Computer Repair       birmingham                         54.3665         0.89336

Thanks for your help with this.

Upvotes: 1

Views: 172

Answers (2)

echo_Me
echo_Me

Reputation: 37253

you may looking for this

    select t1.Title , SUBSTRING_INDEX(t1.Location, ' ', 1)as Location , t2.Latitude  , t2.Longitude 
   from Table1 t1
  inner join
  Table2 t2
   on  SUBSTRING_INDEX(t1.Location, ' ', 1) = t2.Location

OUTPUT:

TITLE               LOCATION       LATITUDE     LONGITUDE
pizza shop          london         53.6658      0.25533
Computer Repair     Birmingham     54.3665      0.89336
Phone Shop          Manchester     66.3368      0.25836

DEMO SQLFIDDLE HERE

with UPDATE statment should be like that

    update Table1 t1
        inner join Table2 t2
        on  SUBSTRING_INDEX(t1.Location, ' ', 1) = t2.Location 
    SET t1.Latitude = t2.Latitude ,
        t1.Longitude = t2.Longitude

DEMO SQLFIDDLE

Upvotes: 1

clapas
clapas

Reputation: 1846

select t1.title, t2.location, t2.latitude, t2.longitude
from table1 t1, table2 t2
where t1.location ilike concat('%', t2.location, '%');

Upvotes: 0

Related Questions