servantis
asked on
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
SELECT * FROM A, B
WHERE A.ID = B.CAST(B.ID1 AS VARCHAR(10)) + CAST(B.ID2 AS VARCHAR(10)) AS ID
SELECT *
FROM A
JOIN B ON A.ID = CAST(B.ID1 AS VARCHAR(10)) + CAST(B.ID2 AS VARCHAR(10))
FROM A
JOIN B ON A.ID = CAST(B.ID1 AS VARCHAR(10)) + CAST(B.ID2 AS VARCHAR(10))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Go with VMontalvao. Joins should be performed in the WHERE clause.
>> Joins should be performed in the WHERE clause. <<
Why? I don't agree
Why? I don't agree
ASKER
Thanks for the replies. VMontalvao gets the points since his syntax is closest to what I'm after.
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 :-)