Best Practice For Lookup Tables In SQL

I am fairly new to SQL so apologies if this questions sounds odd.

I keep coming across the issue of poor data. As an example London could be stored as LON, London UK, London England etc. Prior to using SQL I had lots of Excel lookup tables where in the first column I would have the original, and in the 2nd helper column I would have the corrected version. As an example:

Name             Name_1
London, UK       London
Lon              London
LON              London
London           London
London, England  London
LND              London

​x
 
Name             Name_1London, UK       LondonLon              LondonLON              LondonLondon           LondonLondon, England  LondonLND              London​

Is there a straightforward way of doing this in SQL? I am currently trying to create lookup tables and then use joins. This is becoming tricky as I do not always have corrections for every instance, so in most scenarios my lookup tables have fewer items than the tables I’m joining them to.

I’ve been teaching myself about stored procedures and I wondered if this could solve the problem. Trouble is my searching on the subject of lookup tables is coming up empty.

Any advice or pointers would be gratefully received, even if it is just to say that this cannot be done.

Thanks as always for you help and apologies for the long post.

Answer

You don’t have to do any of the other things, just return the original if you don’t have a translation for it.

SELECT
t1.FirstName,
t1.LookupField,
case when t2.Name_1 is null 
    then t1.lookupfield 
    else t2.name_1 end Name_1
FROM People as t1
LEFT INNER JOIN TableLookupCities as t2
ON t1.LookupField = t2.Name

 
SELECTt1.FirstName,t1.LookupField,case when t2.Name_1 is null     then t1.lookupfield     else t2.name_1 end Name_1FROM People as t1LEFT INNER JOIN TableLookupCities as t2ON t1.LookupField = t2.Name​

Advertisement

Answer