CODEWITHSUNDEEP
sqlsql-serverjoin
shajid
shajid

Reputation: 1

Joining two tables with same column names but with different data abbreviations

Hi I have two tables Table A and Table B .

Table A has 3 columns id ,Location & name

Table B has 3 columns id , location & address

I need to join these two tables . I know we can use the location table to join . But in Table A say for Eg location has data s like Maryland , New york Etc...

But in Table B the location is in short form like MD, NY Etc...

How do i join this ????

Maryland - MD New york- NY

Upvotes: 0

Views: 1220

Answers (3)

nehaJ
nehaJ

Reputation: 77

Query will be

$sql = SELECT A.id,A.location,A.name,B.id,B.location AS location_B,B.Address FROM TableA A INNER JOIN TableB B ON A.id=B.id ";

u will get result from table A with column name - location and FROM table B with column name = location_B

Thanks

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

SELECT *
FROM (SELECT *, CASE location WHEN 'Maryland' THEN 'MD'
                              WHEN 'New york' THEN 'NY'
                    END AS StateCode
     FROM  TABLE_A) A 
INNER JOIN TABLE_B B 
ON A.StateCode = B.location

Upvotes: 0

Richard Vivian
Richard Vivian

Reputation: 1750

You have to join on a matching key between the 2 tables.

If you can get a table with both keys in a table you can use the table as a junction table.

I.E Create a table that as the Key from Table A and Key From Table B

JUNCTION TABLE(TableAKey, TableBKey)

You can then join both tables via this junction table

Upvotes: 1

Related Questions