CODEWITHSUNDEEP
sqlsql-servert-sqljoin
Martin
Martin

Reputation: 375

concat 2 column values on JOIN sql

Table One


ID  Code    Amount
1   2       100
2   2       200


Table Two

ID  Key     Description
1   12      Bag
2   22      Cap

I want to join select two tables on joining 2 columns for one table. Say in table table I want to join them when t1.id + t1. code = t2.key. In graphical I want to get 22 = 22 or 12 = 12 where in 22 or 21 in first side is t1.id+t1.code

Query:

Select * 
from table1 AS t1 INNER JOIN table2 AS t2 ON (t1.id +""+ t1.code)= t2.key

Error:

Msg 1038, Level 15, State 4, Line 1 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

Upvotes: 2

Views: 10151

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176314

You should use '' for empty string:

Select * 
from table1 AS t1 
INNER JOIN table2 AS t2 ON (t1.id +''+ t1.code)= t2.[key]
-- key is reserved keyword so you need to quote it

Or CONCAT:

Select * 
from table1 AS t1 
INNER JOIN table2 AS t2 ON CONCAT(t1.id, t1.code)= t2.[key];

Rextester Demo

If columns are INT you also need to cast them like: CAST(t1.id AS VARCHAR(10)).

Be aware that your join will perform poorly.

Upvotes: 6

Related Questions