• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

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
0
servantis
Asked:
servantis
1 Solution
 
Renante EnteraCommented:
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
 
Vitor MontalvãoMSSQL 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now