SQL server joining where fields different type

Question

SELECT qd.new_capid, qd.new_captype, 
cv.*
FROM quotedetailextensionbase qd 
LEFT JOIN new_capvehicleextensionbase cv
ON qd.new_capid = cv.new_capid 
AND qd.new_captype = cv.new_type

A pretty straightforward join, except the last line

AND qd.new_captype = cv.new_type

qd.new_captype is varchar and can either be "car" or "van" cv.new_type is int and can either be 1 or 2

car = 1 van = 2

Is there some way I could "translate" one to the other to do the join?

Thanks

huh. Thanks. I never knew there was a difference... http://stackoverflow.com/questions/12436859/case-expression-vs-case-statement — Tab Alleman, Oct 29 '15 at 14:19

score 1 · Answer 1 · answered Oct 29 '15 at 14:11

1

If there can be only 2 values you can use CASE in following:

AND case qd.new_captype when 'car' then 1 when 'van' then 2 else 0 end = cv.new_type

answered Oct 29 '15 at 14:11

Stanislovas Kalašnikovas

9,203
4
32
59

Sam Cohen-Devries · Accepted Answer · 2015-10-29T14:23:24.723

1

You can join on a CASE expression:

SELECT qd.new_capid, qd.new_captype, 
cv.*
FROM quotedetailextensionbase qd 
LEFT JOIN new_capvehicleextensionbase cv
ON qd.new_capid = cv.new_capid 
AND (CASE qd.new_captype when 'car' then 1 when 'van' then 2 end) = cv.new_type

edited Oct 29 '15 at 14:23

answered Oct 29 '15 at 14:11

Sam Cohen-Devries

2,025
2
18
35

SQL server joining where fields different type

2 Answers2