1) These tables were designed to have this type of logic used against them

is it too late to go back and shoot the person who did this? it won’t solve your problem, but it might make you feel better

actually, the person who should be shot is the manager of the person who thought this scheme up

2) Yes, I do know this isn’t the best way to do it…

good news, you are therefore open to suggestions, right?

3) The second table is fairly large, about 20-30 reasons, so creating every possible combination to populate a third table is daunting.

do a cross-join with itself, and voila, all 400-900 combinations are easy to obtain

are there any scenarios where the reason code is comprised of more than two reasons? because then you’re in a real pickle

———————————————————————————————————————————————
This appears to be a bitmask. This is used in code to decode multiple errors from a returning function(that can only return one value). Each possible value in the list of reasons should be a power of 2. (0,1,2,4,8…).

What database is this in? That’s because the solution will be database dependent. In Oracle the function is BITAND. Your query would look something like this:

select fn,ln,reason_detail
from first_table, second_table
where bitand(first_table.reasons,second_table.reason) = second_table.reason;

Try that and see if it works.

-Dave

P.S. Just looked it up in SQL Server the bitwise and operator is a ‘&’. So the above query in SQL Server looks like this:

select fn,ln,reason_detail
from first_table
inner join second_table on (first_table.reasons & second_table.reason) = second_table.reason;