MathFan MathFan - 10 months ago 50
SQL Question

Join does not work on two identical fields

I am trying to join two tables on sql server 2008.

select *
from TABLE1 a
left join TABLE2 b
on a.cont_id = b.cont_id

However, it is not able to connect any of the data.

a.cont_id is of nvarchar(255) type and b.cont_id is varchar(80). Using CAST in any way didn't help.

As a.cont_id consists of only numerical data, at first I used type float and integer and applied cast function, but that didn't solve anything.

I checked their varbinary codes by using the following query

select cont_id, CAST(cont_id AS varbinary) AS BinaryCode
from Table1
where cont_id = '53126124'

select cont_id, CAST(cont_id AS varbinary) AS BinaryCode
from Table2
where cont_id = '53126124'

and I got these two results

cont_id BinaryCode
53126124 0x4189551F60000000

cont_id BinaryCode
53126124 0x3533313236313234

One more information - Table 1 is imported from the excel document that was created in neighbour country (Czech republic).

As for the source tables, here is an example, if it helps.

cont_id C1 C2 C3
53126124 3 31 510
175553321 5 16 510


dt cont_id D1 D2
201610 53126124 0 0
201610 175553321 41 520

Do you have any idea how to deal with this?

Answer Source

Okay, I think I've got it:



results in 0x4189551F60000000, while this

SELECT CAST('53126124' AS varbinary)

results in 0x3533313236313234, while this

SELECT CAST(53126124 AS varbinary)

comes back with 0x032AA3EC.

The first value is the varbinary representation of a FLOAT, the second of a VARCHAR and the last of an INT.

Obviously the first IS NOT VARCHAR!

Float types tend to be unprecise, there might be a tiny value like 1,0000000001 or 0.999999998 which is considered as 1, but is not equal.

You might try with this:

select *
from TABLE1 a
left join TABLE2 b
on CAST(ROUND(a.cont_id,0) AS INT) = CAST(ROUND(b.cont_id,0) AS INT)