MySQL syntax for order by not accepted in SQL Server
Your [sub_tree] derived table is using an ORDER BY with no TOP operator.

Try something like the following:

    SELECT TOP (1) node.name
    [...]
    ORDER BY node.lft
) AS sub_tree


Granted that you are just following a MySQL book, I should probably mention the whole "no-no" on doing old style joins on SQL Server. Use INNER JOIN, LEFT JOIN, etc rather than FROM a,b,c WHERE a.id =b.id.