title: SQL Conditional join using different join types and external parameter
body: <p>Despite looking at related conditional join questions, I haven't been able to do the following.</p>
  
  <p>I have table <code>t</code> with columns <code>ID</code> and <code>Value</code>, and an external parameter <code>@Option</code>.</p>
  
  <p>If <code>@Option = 'a'</code> I would like to join onto table <code>a</code> which has columns <code>ID</code> and <code>Flag</code>, where <code>Flag</code> is Y/N.</p>
  
  <p>If <code>@Option = 'b'</code> I would like to join onto table <code>b</code> which just has an <code>ID</code> column, and the existence of a matching ID indicates that <code>Flag='Y'</code>.</p>
  
  <p>So what I want is something like</p>
  
  <pre><code>IF @Option = 'a'
  
  SELECT 
    t.ID
    ,t.Value
    ,a.Flag
  FROM t
  INNER JOIN a 
    ON t.ID = a.ID
  
  ELSE IF @Option = 'b'
  
  SELECT 
    t.ID
    ,t.Value
    ,CASE WHEN b.ID IS NULL THEN 'N' ELSE 'Y' END AS Flag
  FROM t
  LEFT JOIN b 
    ON t.ID = b.ID
  </code></pre>
  
  <p>but I'm not sure how to set up the equivalent of the <code>IF</code> statements.</p>
  
  <p>How can I achieve this?</p>
  
  <p>One unpreferred solution is to ignore the external parameter and return two flag columns, such as:</p>
  
  <pre><code>SELECT 
    t.ID
    ,t.Value
    ,CASE WHEN a.Flag IS NULL THEN 'N' ELSE a.Flag END AS aFlag
    ,CASE WHEN b.ID IS NULL THEN 'N' ELSE 'Y' END AS bFlag
  FROM t
  LEFT JOIN a
    ON t.ID = a.ID 
  LEFT JOIN b
    ON t.ID = b.ID 
  </code></pre>
  
  <p>but I would really prefer to have just the one column, plus this query is slow.</p>