title: Comparing values in MySQL table based on another tables rows
body: <p>I am working on an application system in CakePHP. Each application contains the address and a (UK only) postcode of the applicant.</p>
  
  <p>The client wants to be able to assign "postcode areas" to a given member of their team.
  Unfortunately the postcode area varies in format and length - examples: <code>TA</code>, <code>TQ1</code>, <code>S60</code>, <code>BS22</code>, <code>TA6 4EJ</code></p>
  
  <p>The user wants to be able to specify entire areas, or more specific areas on a per user basis if required.
  <img src="https://i.stack.imgur.com/O1YPR.png" alt="schema"></p>
  
  <p>I need to be able to take each <code>prefix</code> and compare it to the <code>postcode</code> for each application, and return any matches.</p>
  
  <hr>
  
  <p>User <code>Ross</code> manages applications with postcode beginning with: <code>BQ</code>. <code>BS</code> and <code>S60</code></p>
  
  <p>User <code>Dave</code> manages applications with full postcodes: <code>BS22 4JL</code> and <code>TA6 7EJ</code></p>
  
  <hr>
  
  <p>I figured that the best way would be to have a table full of user-defined postcode prefixes:</p>
  
  <pre><code>   id  |  prefix
      1  |      TQ
      2  |      TL
      3  |     S50
      4  |     S55
      5  | TA6 4EJ
  </code></pre>
  
  <p>Each user is assigned a postcode area to manage. </p>
  
  <p>I'm thinking if I can compare each application postcode to each prefix, I can retrieve all applications in a given postcode area, then look up the user that this belongs.</p>
  
  <pre><code>id | postcode
  1  |  tq33 abc
  2  |  TL5  8HA
  3  |  s50 11l
  </code></pre>
  
  <p>The postcode data isn't in any standard/regular format unfortunately; so this might get tricky.
  It sounds like I need to <code>JOIN</code> the <code>prefix</code> on to the postcode; but as there's no key I don't think that's possible.</p>
  
  <p>There's no direct association between <code>prefix</code> and <code>postcode</code>. Feel like I'm missing something fundamental </p>
  
  <p>Any pointers?</p>