How to do join in a SQL query using a bit masked value

Question

I am have trouble finding a good example SQL query to do a join on two tables where the value being joined on is a bit mask value.

Region Table

RegionBitmask	RegionName
1	North
2	East
4	South
8	West

Store Region XRef Table

StoreID	StoreCode	StoreRegion
1	100	3
2	101	1
3	102	9
4	103	8
5	104	12

Such that I would get results back like:

StoreID	StoreCode	StoreRegion	RegionBitmask	RegionName
1	100	3	1	North
1	100	3	2	East
2	101	1	1	North
3	102	9	1	North
3	102	9	8	West
4	103	8	8	West
5	104	12	4	South
5	104	12	8	West

select *
from StoreRegionXRef
     left join Region on StoreRegion & RegionBitmask = StoreRegion

Does not provide expected results, always seem to get a subset of expected results or no results.

Answer 1

Using the & (bitwise AND) operator you can join between your StoreRegionXref and StoreRegion table:

SELECT *
from StoreRegionXRef 
  left join StoreRegion on StoreRegionXref.StoreRegion & StoreRegion.RegionBitmask > 0

You can see a dbfiddle of this in action here

This logic checks your bitmask to see if the corresponding bit in your StoreRegion table is present in the bitmask.