Is it possible to join two tables on one column = concatenation 2 columns?

Table A has column X, which is an int made up of the concatenation of columns Y and Z (which are both floats) in table B. I want to join tables A and B in a manner similar to this开发者_开发知识库:

select *
from tableA a inner join tableB b
on a.X = b.cast(concat(cast(b.Y as varchar), cast(b.Z as varchar)) as integer

Except that, obviously, my example is not correctly done.

You can do this:

select * 
from tableA a 
inner join tableB b 
on a.X = cast(cast(b.Y as varchar) + cast(b.Z as varchar) as int)

If either of your floats have decimal points though, the conversion to int will fail.

E.g., this works:

declare @f1 as float
declare @f2 as float
set @f1 = 1
set @f2 = 7
select cast(cast(@f1 as varchar) + cast(@f2 as varchar) as int)

Output: 17

But this does not:

declare @f1 as float
declare @f2 as float
set @f1 = 1.3
set @f2 = 7
select cast(cast(@f1 as varchar) + cast(@f2 as varchar) as int)

Output: Conversion failed when converting the varchar value '1.37' to data type int.

Sounds like a job for a computed column, then it would be index-able.

http://www.mssqltips.com/tip.asp?tip=1682

Can you create another column in b named x which contains the value you want? Then the join to A is easy.