Cast and concatenate two fields for use in where condition

I need to do an equi join on two tables A and B where the ID in A is a concatenation of ID1 and ID2 in table B. I've tried the following but I'm sure the syntax is incorrect.

SELECT * FROM A, B

WHERE A.ID = B.CAST(B.ID1 AS VARCHAR(10)) + CAST(B.ID2 AS VARCHAR(10)) AS ID
servantisAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Yes, the syntax you used is wrong. The right syntax should be:

SELECT * FROM A, B
WHERE A.ID = CAST(B.ID1 AS VARCHAR(10)) + CAST(B.ID2 AS VARCHAR(10))

Explaination:
- "B.CAST" it's mean a field named CAST in B table, but you want the CAST function
- "AS ID" is an alias, but you can't create alias in criteria

Test it and see if it runs now. Good luck
0
 
Renante EnteraSenior PHP DeveloperCommented:
Hi servantis!

You can simply do it like this :

SELECT *
FROM A
INNER JOIN B
  ON (CAST(B.ID1 AS VARCHAR(10))+CAST(B.ID2 AS VARCHAR(10))) = A.ID

Hope this helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 
adatheladCommented:
SELECT *
FROM A
    JOIN B ON A.ID = CAST(B.ID1 AS VARCHAR(10)) + CAST(B.ID2 AS VARCHAR(10))
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TDSnetCommented:
Go with VMontalvao.  Joins should be performed in the WHERE clause.
0
 
adatheladCommented:
>> Joins should be performed in the WHERE clause. <<
Why? I don't agree
0
 
servantisAuthor Commented:
Thanks for the replies. VMontalvao gets the points since his syntax is closest to what I'm after.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.