title: SQL to select records where two values in one table are both present as substrings in a text field in a second table
body: <pre><code>TableOne:    LNAME      FNAME     FILETYPE
               Smith      John      A
               Jones      Mary      B
               Brown      James     B
  
  TableTwo:    NAME                 FILETYPE
               Mary Smith           1
               Smith, Mary Jane     2
               Brown, James         3
  </code></pre>
  
  <p>Tricky one to explain.</p>
  
  <p>I have two tables (not huge - several thousand records). The relevant fields are in the example above. What I need to pull out would be, algorithmically:</p>
  
  <pre><code>SELECT
      TableOne.LName, TableOne.FName, TableOne.FileType, TableTwo.Name
  WHERE
      (TableTwo.Name LIKE *LName* AND TableTwo.name LIKE *FName*)
      AND
      (IF TableOne FileType = 'A' THEN
          TableTwo.FileType = '1'
       ELSE IF TableOne FileType = 'B' THEN
          TableTwo.FileType = '2' OR TableTwo.FileType = '3'
      )
  </code></pre>
  
  <p>Obviously that's not a solution, just a clumsy type of psuedocode.
  I've not posted any of my attempts from the last couple of hours because they've not even come close (they've involved temporary tables and sub-queries).</p>
  
  <p>I know that this is never going to be 100% accurate - there are too many typos in that NAME field to start with - I just need to find a starting point based on records where there is a reasonable chance that there is no entry in TableTwo to match up with TableOne.</p>
  
  <p>I'm tempted to give up and just do the job in Python (I'd like to think I could have done it in the time it's taken to type this), but I'd like to see if it can be done in SQL      </p>