Getting the first URL alias for a node in a SQL query

Question

For some backups and exports, I need to fetch the URL-aliases for each node in SQL. The url_alias taqble, however, has no foreign key to the node table (nid) nor the other way around, it seems.

Is there some hidden join-table somewhere? A neat trick to fetch the node and its alias in a JOIN?

Answer 1

Since the value in the src column in the url_alias table is predictable for nodes you can just query it directly, without the need for any joins:

$nid = 1; // node id
$src = 'node/' . $nid;
$query = db_query("SELECT dst FROM {url_alias} WHERE src = '%s'", $src);

If you definitely want to use a join though, something like this will work:

SELECT n.nid, ua.dst
FROM {node} n 
INNER JOIN {url_alias} ua ON ua.src = CONCAT('node/', n.nid)