Forcing a hash join on a temporary table generates error I have the following query:

select
    afp.period_start
    , afp.period_end
    , COUNT(*)
from services s
    Left join
        (
            select entityId, resolutionDate
            from ...             
        ) SI on si.entityId = s.Id
    left join #aff_periods afp -- or left hash join #aff_periods afp
        on isnull(si.reslutionDate, GETDATE()) &lt;= afp.period_end
group by afp.period_start
    , afp.period_end
option (hash join); -- or this


Whenever adding either the query-level hint or join-level hint I get the following error.


  Msg 8622, Level 16, State 1, Line 50 
  
  Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.


I've read some posts online suggesting that this might be because of a index on the table, so I dropped the non-clustered index I had created on that table and tried to re-run the query but I still get the error.

Any suggestions what I could try or why this is happening for a temp table?
What you're running into isn't because of the temp table, it's because of the type of join you've chosen with the hint you're forcing. 

Both Merge and Hash joins require at least one equality predicate to run. 

For more information, check out these old (but awesome) blog posts from Craig Freedman.

Introduction to Joins

Nested Loops Join

Merge Join

Hash Join