How to map url_alias table to node table? I was trying to use some SQL to map the url_alias field to my node field on the url_alias.pid = node.nid field. However, it turns out that these are not apparently related, sometimes they are the same value and sometimes they aren't. As I'm looking through my mysql db I can see no connection between the url_alias table and the node table. 

The url alias is coming from the pathauto module. I just want to create links to my resources when the titles occur in a sql query in a custom module...this seemed the easiest way to do it.

Does anyone have any idea on which fields or tables the connections occur between these two entities?
They are not related because url_alias table contains all the URL aliases. You can use MySQL CONCAT to concatenate strings (fields or string literals). 

$results = db_query("
  SELECT 
    n.nid, n.title, 
    a.source, a.alias 
  FROM {node} n 
  LEFT JOIN {url_alias} a 
    ON CONCAT('node/', nid) = source
  ")-&gt;fetchAll();
foreach ($results as $result) {
  dpm($result);
}