troubles with SQL syntax

trying to do join on url_alias for node/#### where only #### is avail and only within the SQL itself