title: MySQL join on only part of a field using Substring and Like
body: <p>I have two tables that look like this:</p>
  
  <pre><code>node:
  NID    Name
  1      Chicago
  2      New York
  3      LA
  
  url_alias:
  src    dst
  user/1    user/Bob
  user/2    user/Mike
  node/1    node/chicago
  node/2    node/new-york
  node/3    node/LA
  </code></pre>
  
  <p>etc</p>
  
  <p>Basically, the url_alias table has alias urls that my webserver redirects to.  Both nodes and users have 'ids' as seen in the url.  I am trying to join my node table to the url_alias table and match on the stuff after the '/' (the node id).  So far I have this code, but it only uses substring and not like so it also matches any users that  end in the correct number:</p>
  
  <pre><code>select `node`.`nid` AS `nid`,`node`.`name` AS `name`,`url_alias`.`src` AS `srcurl`,`url_alias`.`dst` AS `dsturl`
  from ((`node` join `url_alias` on ((`node`.`nid` = substring(`url_alias`.`src`,6)))))
  order by `node`.`nid` desc
  </code></pre>
  
  <p>(actually the query is a bit longer as it also joins another table, which is working, but I have edited it for simplicity)</p>
  
  <p>This query returns results like this:</p>
  
  <pre><code>nid    name      srcurl    dsturl
  
  1      chicago   node/1    node/chicago
  1      chicago   user/1    user/Bob
  2      new-york   node/1    node/new-york
  2      new-york   user/1    user/Mike
  </code></pre>
  
  <p>Is there a way to use "LIKE 'node/%'" within my query, or something similar to only grab 'nodes' before substring runs its matching?</p>