Hi Daniel!

>select r.id, a.node node, route_index, b.node link_node, c.quality_label
>from routes r
> join nodes a on r.node_id = a.id
> join nodes b on r.link_id = b.id
> join route_qualities c on r.quality = c.QUALITY
> order by node, route_index;
>
>Is there an alternate way of writing this with subqueries instead of
>joins?

I've assumed route_index is part of the routes table, and that a.id, b.id and c.quality are unique in each table.

select r.id,
(select a.node from nodes a where r.node_id = a.id) node, route_index,
(select b.node from nodes b where r.link_id = b.id) link_node,
(select c.quality_label from route_qualities c where r.quality = c.QUALITY)
from routes r
where exists(select * from nodes a where r.node_id = a.id) and
exists(select * from nodes b where r.link_id = b.id) and
exists(select * from route_qualities c where r.quality = c.QUALITY)
order by 2, 3;

>If so, does that offer any advantages?

No, not really. The only benefit I can possibly imagine, is that the subselects make it very clear that there's never more than 1 row returned from a, b and c and that ROUTES should be considered the main table of the query (but those two 'advantages' could easily be 'fixed' with a comment or two in the query). A possible drawback could be performancewise, I think the subselects could be equally quick, but never quicker (it would vary depending on your tables).

HTH,
Set