CODEWITHSUNDEEP
mysqlsql
Arie
Arie

Reputation: 3563

Inner join based on specific condition

Tables are as follows:

Tb_RefCity (both columns varchars)

RealCity    |   RefCity
------------------------
London        00 Perfor-Lon
Amsterdam     Ort Amster

Tb_RawData (both columns varchars)

Counter     |   FakeCity
------------------------
222234      |   00 Perfor-Lon (2342)
342344      |   00 Perfor-Lon(23L)
534534      |   00 Perfor-Lon (342211
234444      |   Ort Amster(761)
234234      |   Ort Amster L1)

I want to inner join Tb_RawData with Tb_RefCity to insert RealCity into City column and counter values to Tb_FinalTable

Result should look like this (example)

Tb_FinalTable (Counter = int / City = varchar)

Counter     |   City
-----------------------
222234      |   London
342344      |   London
534534      |   London
234444      |   Amsterdam
234234      |   Amsterdam

My current query:

INSERT INTO Tb_FinalTable
SELECT Raw.Counter, Ref.RealCity 
FROM Tb_RawData As Raw 
INNER JOIN Tb_RefCity As Ref ON Raw.FakeCity = Ref.RefCity

Problem:

The problem is in Tb_RawData FakeCity's could have diffrent ending strings, however starting point is always the same for specific city e.g :

00 Perfor-Lon
Ort Amster

I would like my query to be independent for ending strings in FakeCity and in inner join to check starting strings comparing with Ref table to get real city. Hope you get my point.

Upvotes: 0

Views: 52

Answers (2)

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/cae0da/1

INSERT INTO Tb_FinalTable
SELECT Raw.Counter, Ref.RealCity 
FROM Tb_RawData As Raw 
INNER JOIN Tb_RefCity As Ref 
ON Raw.FakeCity LIKE CONCAT(Ref.RefCity,"%");

Upvotes: 1

SBFrancies
SBFrancies

Reputation: 4240

Try this:

SELECT Raw.Counter, Ref.RealCity FROM Tb_RawData As Raw INNER JOIN Tb_RefCity 
As Ref ON Raw.FakeCity LIKE CONCAT(Ref.RefCity, '%')

Upvotes: 1

Related Questions