title: Inner Join based on column value in SQL Server 2008
body: <p>I would like to join two tables based on a column value </p>
  
  <ul>
  <li>if <code>pm.ServiceLevelID</code> value is 1 or <code>NULL</code> then inner join on <code>u.FacilityId</code> </li>
  <li>if <code>pm.ServiceLevelID</code> value is 2 then inner join on <code>u.FacilityServiceId</code> </li>
  </ul>
  
  <p>These two tables <code>pm, u</code> have these columns:</p>
  
  <pre><code>ProviderMessages
    MessageID, FacilityServiceID, ServiceLevelID, FacilityID, ProviderTypeID
  
  User_FA
    FacilityServiceId, UserFacilityID, FacilityId
  </code></pre>
  
  <p>Currently, I have this <code>INNER JOIN</code> </p>
  
  <pre><code>SELECT DISTINCT 
      MessageID, UserFacilityID, 9    
  FROM 
      #ProviderMessages   
  INNER JOIN 
      #User_FA ON (#User_FA.FacilityId = #ProviderMessages.FacilityID OR  
                                     #ProviderMessages.FacilityID IS NULL)
               AND (#User_FA.FacilityServiceId = #ProviderMessages.FacilityServiceID)
  </code></pre>