title: SQL find value in one table in another variation
body: <p>I have 2 tables like so.</p>
  
  <pre><code>Table1
  FullName     LastName    FirstName
  John Smith   Smith       John
  
  Table2
  LastName     Firstname   AKA
  Smith Stein  Johnny      John the Bad Guy
  </code></pre>
  
  <p>I need to find any variation of <code>lastname</code> and <code>firstname</code> from <code>table1</code> in <code>table2</code>. However, I can't join the tables because there is nothing to join on as the names could be different. Basically, I'm trying to compare our list to a public list to see if any names match up. </p>
  
  <p>I am trying this:</p>
  
  <pre><code>Select * 
  from table1
  where exists (select * 
      from table2 
      where charindex(table1.lastname, table2.lastname)&gt;0 
          and charindex(table1.firstname,table2.firstname)&gt;0)
  </code></pre>
  
  <p>I get returned value, but I'd like to see the values from table2 as well.</p>
  
  <p>I would need to see John Smith Returned AND show that John Smith Stein was flagged in the other table. This variation of lastname would also apply to the firstname as well. That's why I can't join on any 1 specific column. </p>
  
  <p>Sorry if this is confusing.</p>