Joining on Columns of Different Type

Joining Different Data Types

If ProductNumber can contain MORE than the StoreProductNumber:

LEFT JOIN b 
ON a.ProductNumber LIKE '%' + CAST(b.StoreProductNumber as varchar(n)) + '%'

Obviously you can modify n to make sure your StoreProductNumber isn't truncated.

However, if they're guaranteed to be the SAME (just different datatypes), you can just compare them directly:

LEFT JOIN b
on CAST(a.ProductNumber as BIGINT) = b.StoreProductNumber

And if you don't need a BIGINT, you can use an INT or whatever datatype you require.

Lastly, as HLGEM pointed out, SQL will do implicit conversions for you, so technically this would also work:

LEFT JOIN b
on a.ProductNumber = b.StoreProductNumber

But, I prefer to do all conversions explicitly for clarity, so I suggest against this approach.

How to join tables together on columns with different datatypes?

Within Access you could use the CLng (or Cint) function to convert the Table A's REFERENCE values from text to number.

I would prefer to create a view of Table A in SQL Server to transform the field's data type before Access gets the data. You shouldn't need to test the view against your other existing apps. When your re-write make the view no longer useful, just discard it.

SQL: Join two tables with different type of columns

I would suggest normalizing the schema as @Sodmond suggested. However, if this is not an option, you could use find_in_set for the join condition - it will implicitly convert the int from table2 to a character:

SELECT t1.OrderID, RetailerName, OrderDate, Product
FROM   table1 t1
JOIN   table2 t2 ON FIND_IN_SET(t2.sku, t1.skus) > 0

Join two tables by matching the two columns with different data type

You can go for CASE expression and convert language_id to language and do the JOIN as given below:

SELECT t1.column1,t1.column2, t2.columnA, t2.columnB
FROM       Table1 AS t1
INNER JOIN 
            (SELECT ColumnA, ColumnB, 
                    CASE Language_ID WHEN 1 THEN 'English' 
                                     WHEN 2 THEN 'French' 
                     End AS Language 
             FROM Table2) AS t2
ON t1.Language = t2.Language

pandas: Join two data frames on columns of different types

Try keeping df_A.a_number as Int64 and convert the key in df_B.b_number to Int64 as well.

df_B.b_number.astype('int')
df_C = pd.merge(df_A,df_B, how ='inner', left_on = ['a_number'], right_on = ['b_number'])

---

Related Topics