join tables on columns that have different data

Question

join tables on columns that have different data

robert.wiglesworth

SSCommitted

Points: 1790
More actions
July 18, 2013 at 10:15 am

#295703

I have two tables that have a column representing the same value, but entered differently and I need to join them in a query. What I mean is this:
tableA has a column called 'city' and many rows with a value of 'Boston'
tableB has a column called 'city' and many rows with a value of 'BO' (BO stands for Boston in this system)
I need to join on these columns, but I cannot do:
join on tableA.city = tableB.city
because the values are not the same. But they represent the same so there has to be a way to do this join.
Any help?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 1

The only way this can be done is by building another table with a cross reference of values. Does something like that already exist in your system?

robert.wiglesworth SSCommitted Points: 1790 More actions · Answer 2

Unfortunately that does not exist. I could create such a table, I suppose. I was reading something about Common Table Expressions (CTE) but I am not familiar with them....

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 3

If it were me I would just build it in it's own table because it will likely have to be used again, but you can build it on the fly in a cte. It would look something like this:

with myxref (city, abbr) as

(

select 'Boston', 'BO'

union all select 'New York', 'NY'

)

select *

from tableA a

join myxref x on x.city = a.city

join tableB b on b.city = x.abbr

Is there some sort of logic to how the abbreviations are assigned, like the first two letters of the city, or are they just chosen by someone?

calvo SSChampion Points: 12930 More actions · Answer 4

Something like this

SELECT

a.name,

b.name

FROM table1 a

inner join table 2 b

on a.name = case when b.name = 'BO' THEN 'Boston'

WHEN b.name = 'bos' THEN 'Boston'

WHEN b.name = 'NY' THEN 'New York'

ELSE 'na'

END

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

robert.wiglesworth SSCommitted Points: 1790 More actions · Answer 5

Great information from all of you, thank you. I will try all of these and see what works the best in this situation.

Molap Mr or Mrs. 500 Points: 507 More actions · Answer 6

you could also do something like WHERE left(TableA.city,2) = TableB.city and TableA.city = 'Boston'

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

Molap (7/18/2013)
you could also do something like WHERE left(TableA.city,2) = TableB.city and TableA.city = 'Boston'

That will only work if the 2 letter code for each city is the first 2 letters of the city name.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]