title: How can I dynamically choose which column to join on in SQL?
body: <p>Say I have the following table in SQL Server (2008):</p>
  
  <pre><code>Person
  
  |PersonID|NickName|FirstName|LastName|
  |1       |Jim     |James    |Leahy   |
  |2       |Mike    |Michael  |Ross    |
  |3       |Bob     |Robert   |Helberg |
  </code></pre>
  
  <p>I want to know if the following is possible in SQL. I have a main table and I would like to find matches on another table based on the <code>NickName</code> and <code>FirstName</code> columns. However, I want the columns to be joined to in a specific order. </p>
  
  <p>I want to join on the first column from above (<code>NickName</code> or <code>FirstName</code>) which will match the identifier in the table below</p>
  
  <pre><code>|Identifier|PersonId|
  |Jim       |1       | &lt;- should return PersonId = 1
  |Michael   |2       | &lt;- should return PersonId = 2
  </code></pre>
  
  <p>So if there is a match on <code>NickName</code> then choose the row. If there is no match on <code>NickName</code> then look at <code>FirstName</code>.</p>
  
  <p>Is there any way I can query on <code>NickName</code> and <code>FirstName</code> columns in a particular order?</p>
  
  <p>I don't think <code>COALESCE</code> will work since we are not guaranteed that any of the columns will be <code>NULL</code> - we only know that a match may not occur on the column instead.</p>
  
  <p><em>Please let me know if you need clarification; I may not have worded this well.</em></p>