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
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
