With correction for one or two typos - those both worked, thank you!
Had to read, and read, and read - to try to understand HOW these work.
Which was exactly what I was hoping for - to better understand usage of
Firebird SQL.

Looking at the query plans, it appears Alternative 2 - which uses the
two exists(select) constructs instead of two joins appears to be
slightly more efficient - at least the query plan appears slightly
smaller. So I'll file Alternate 1 for future testing against a larger
dataset - and use Alternate 2 for now.

I'm assuming there would be no benefit, and would probably be worse, to
add indexes for ROUTE_INDEX, or NETCON, or QUALITY? Because they
represent a small range of possible values that is duplicated for each
node?

--
Daniel

------ Original Message ------
From: "setysvar setysvar@... [firebird-support]"
<firebird-support@yahoogroups.com>
To: firebird-support@yahoogroups.com
Sent: 12/19/2015 2:14:23 AM
Subject: Re: [firebird-support] select parent based on multiple child
conditions

>Here's two alternatives for you, they should give the same result
>(though provide different options for modifications, see the comments
>inside the SQL), pick the one you prefer. Both differ from your SQL in
>that rows are also returned when there are no routes, whereas your SQL
>would require at least one route to get a result.
>
>Alternative 1)
>select distinct n.NODE
>from NODES n
>left join ROUTES r on n.ID = r.NODE_ID --remove LEFT from this touple
>if you don't want to return NODES with no route.
>left join ROUTES r2 on n.ID = r2.NODE_ID and r.ID <> r2.ID
>where ((r.ROUTE_INDEX in (1, 2)
> and (r.NETCON>5 or r.QUALITY>3))
> or r2.ID is null
>
>Alternative 2)
>select distinct NODE --remove distinct if you want one row for each
>record in the NODES table, even when they have the same value for NODE.
>from NODES n
>where exists(select * from routes r
> where n.ID = r.NODE_ID
> and r.ROUTE_INDEX in (1, 2)
> and r.NETCON>5 or r.QUALITY>3)
> or not exists(select * from routes r
> join routes r2 on r.NODE_ID = r2.NODE_ID
> and r.ID < r2.ID
> where n.ID = r.NODE_ID)
>
>If you prefer alternative 2 and want only touples with exactly one
>route, the NOT EXISTS can be changed to:
> or exists(select * from routes r
> left join routes r2 on r.NODE_ID = r2.NODE_ID
> and r.ID < r2.ID
> where n.ID = r.NODE_ID
> and r2.ID is null)
>
>HTH,
>Set
>
>
>------------------------------------
>Posted by: setysvar <setysvar@...>
>------------------------------------
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>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
>
>
>