none
How to join 2 tables with same columns but different values RRS feed

  • Question

  • Hi,

    I have 2 tables with columns names as locationnum and storenum both are same but the values are represented differently.


    for example locationnum is 3020 where as store number will be 0000000020.

    I have to join these 2 tables on this column, Can someone please help me with this?

    Thanks

    Monday, September 30, 2013 1:32 PM

Answers

  • Hi Khaleed,

    I made it work , by using this logic 

    SELECT        *
    FROM            Staging.tODS_Location 
    INNER JOIN
                    Staging.tODS_Store 
          ON        
    case
    when convert(int,tODS_Store.Store_Nbr) > 10000
    then convert(int,tODS_Store.Store_Nbr)
    else 3000 + convert(int,tODS_Store.Store_Nbr)
       end
       ) = tODS_Location.Location_Num

    Thanks for your help and time.

    • Marked as answer by SqlDev12 Monday, September 30, 2013 3:53 PM
    Monday, September 30, 2013 3:52 PM

All replies

  • Hi,

    select * from table1 inner join table2 on right(locationnum,2) = right(storenum ,2)

    try this please;



    • Proposed as answer by Guillaume Fraiture Monday, September 30, 2013 1:37 PM
    • Edited by KH MR Monday, September 30, 2013 2:39 PM
    Monday, September 30, 2013 1:36 PM
  • SELECT        Table_1.locationnum, Table_1.storenum, Table_2.storenum AS storenum2, Table_2.locationnum AS locationnum2
    FROM            Table_1 INNER JOIN
                             Table_2 ON Table_1.locationnum = Table_2.locationnum

    or you can use union to get data from both tables

    Select 'Table1', * from Table_1
    union 
    Select 'Table2', * from Table_2


    Please remember to 'Mark as Answer' the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Monday, September 30, 2013 1:42 PM
  • Hi Khaled, It gives me like 1000's of records even if i use distinct keyword and these columns are indeed the same except the 2 which we are trying to match with different numbers.

    Can you let me know if we ned some other code?

    Thanks

    Monday, September 30, 2013 2:15 PM
  • Hi Ahmed,

    The first one doesnt work as table 1 has location_num and table 2 has store_num and the values in both are diff as location_num  = 3020 and store_num = 0000000020.

    Can you please let me know how to join these 2?

    Thanks

    Monday, September 30, 2013 2:16 PM
  • Hi,

    Can you let know the different possibilities that you have and explain the logic to match the columns of the first table and the second one so we will try to help you.


    • Edited by KH MR Monday, September 30, 2013 2:33 PM
    Monday, September 30, 2013 2:28 PM
  • Hi Khaleed,

    for example locationnum is 3020 where as store number will be 0000000020.

    So now i need to change the storenumber to be 3020 same as location number and join it.Can you please help me how to do that?

    Thanks

    Monday, September 30, 2013 2:57 PM
  • Hi, you have to update the store nuber like this:

    update table2 set storenumber  = 3020 where storenumber  = 0000000020

    Then :

    select * from table1 t1 inner join table2 t2  on t1.locationnum  = t2.storenumber

    • Proposed as answer by Ahmed-Samy Monday, September 30, 2013 3:11 PM
    Monday, September 30, 2013 3:07 PM
  • Ah, Ok now I got what you mean

    SELECT        locationnum, storenum
    FROM            Table_1 INNER JOIN
                             Table_2 ON RIGHT(locationnum,3) =RIGHT(storenum,3)
    WHERE locationnum = 3020


    Please remember to 'Mark as Answer' the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Monday, September 30, 2013 3:10 PM
  • Hi Khaled,

    I cannot update the table which has the store number and this table has more than 300 records with similar type, Can you please let me know if there is any way we can do this in join while we join these 2 tables?

    Thanks

    Monday, September 30, 2013 3:11 PM
  • Hi Khaleed,

    It worked out weel ,but i have a small issue here, so like i said the store number 3020 must be equal to 000000020, and its working fine with this condition, however i also have store number as 96101 and it must match to 0000096101 ,instead its matching to 0000096101 as well as 0000000010,0000097101,0000098101,0000099101.

    Can you please help me with this?

    Thanks

    Monday, September 30, 2013 3:19 PM
  • Hi,

    the problème here is that there is not a clear logic to match the rows of your query how can i explain the relation between to values of the column storenumber and the column locationnum ,

    if i'ts possible you can add a mapping table to match the storenumber  and the locationnum

    like this

    create table #maptable (locationnum varchar(10),storenumber  varchar(10))

    insert into #maptable values ('3020','00000020') ....

    so the join wil be like this ;

    select * from #maptable M inner join table1 T1 on t1.locationnum = M.locationnum inner join table2 T2 on t2.storenum = M.storenum


    • Edited by KH MR Monday, September 30, 2013 3:34 PM
    Monday, September 30, 2013 3:33 PM
  • Hi Khaleed,

    I made it work , by using this logic 

    SELECT        *
    FROM            Staging.tODS_Location 
    INNER JOIN
                    Staging.tODS_Store 
          ON        
    case
    when convert(int,tODS_Store.Store_Nbr) > 10000
    then convert(int,tODS_Store.Store_Nbr)
    else 3000 + convert(int,tODS_Store.Store_Nbr)
       end
       ) = tODS_Location.Location_Num

    Thanks for your help and time.

    • Marked as answer by SqlDev12 Monday, September 30, 2013 3:53 PM
    Monday, September 30, 2013 3:52 PM
  • Hi  sujith good work , please vote as heplful ;)

    Monday, September 30, 2013 4:57 PM