title: SQL conditional join with subselect
body: <p>I do not understand why this code isn't working.  I have a join which needs a different join condition depending on the result of a subselect.  </p>
  
  <p>When the term date is null, I want this to come after the and operator:  and vs.term_date is null
  When the term date is not null, I want the subquery below to come after the and operator.  I need to join on the max effective date:  vs.eff_date = (subquery)</p>
  
  <p>I realize this code is messy, but I have to aggregate with a subselect since there are multiple rows coming back and I only want one.  (but open to other solutions)</p>
  
  <pre><code>select *
  From loc
  inner join VendorSite vs
      on loc.Record_Number = vs.SITE_NO
      and --conditional join
          case
              when        
              (
                  case
                      when    --if term date is null i want this after "and"
                          (select top 1 TERM_DATE
                          from VendorSite vs2
                          where vs2.SITE_NO = vs.SITE_NO
                          order by TERM_DATE asc) is null
                      then 1
                      else 0  
                  end 
              ) = 1
              then vs.term_date is null
              else --when term_date isn't null use max eff date
                  vs.eff_date = 
                      (select max(eff_date) 
                      from VendorSite vs2
                      where vs2.SITE_NO = loc.Record_Number)
          end
  </code></pre>
  
  <p>Example:  From the below dataset, I would only want to see the row where term_date is null come back.</p>
  
  <pre><code>site_no eff_date                term_date
  13588   2007-01-01 00:00:00.000 NULL
  13588   2007-03-01 00:00:00.000 2007-11-09 00:00:00.000
  </code></pre>