sql server 2008 - Conditional Joins based on values existing -
would input on following scenario (using mssql08). have 'user' table containing list of 'users', 'currentcode' (2 digit) , 'jobcode' (3 digit). update 'currentcode' field more accurate 3 digit code, have created single column table of these codes called 'newcode'.
the 'newcode' field relates directly 'jobcode' field, updating 'currentcode' users have valid 'jobcode' easy. don't have 'jobcode' (are 'na') i'm doing match of current 2 digit code first 2 digits of listing in 'newcode' table.
i have been able output follows, using 2 joins instances of 'newcode' table ('confirmed' linked 'jobcode' if value exists , 'matched' linked 'currentcode' if not):
+------+-------------+---------+---------------------+-------------------+ | user | currentcode | jobcode | newcode - confirmed | newcode - matched | +------+-------------+---------+---------------------+-------------------+ | | 11 | 111 | 111 | | | b | 12 | na | | 120 | | c | 23 | 232 | 232 | | | d | 42 | 423 | 423 | | | e | 11 | 112 | 112 | | | f | 21 | na | | 210 | | g | 33 | na | | 330 | +------+-------------+---------+---------------------+-------------------+ i'm interested see how combine 2 joins 'newcode' table such return codes in 1 column depending on there being match in 'jobcode' field or not. eg:
+------+-------------+---------+---------+ | user | currentcode | jobcode | newcode | +------+-------------+---------+---------+ | | 11 | 111 | 111 | | b | 12 | na | 120 | | c | 23 | 232 | 232 | | d | 42 | 423 | 423 | | e | 11 | 112 | 112 | | f | 21 | na | 210 | | g | 33 | na | 330 | +------+-------------+---------+---------+ how go creating conditional join 1 table instance, or possible?
conditional join possible, while in case can in simple way using isnull() in select
isnull([newcode - confirmed], [newcode - matched]) newcode
Comments
Post a Comment