Took me a bit to feel my way through, but my first attempt appears to be working:

 

select distinct n.NODE, count(r.NODE_ID) from NODES n
join ROUTES r on n.ID=r.NODE_ID
where (r.ROUTE_INDEX=1 and (r.QUALITY<7)) or (r.ROUTE_INDEX=2 and (r.QUALITY<7))
group by n.NODE
union
SELECT n.NODE, COUNT(r.NODE_ID) FROM NODES n
join ROUTES r on n.ID=r.NODE_ID
GROUP BY n.NODE
HAVING COUNT(r.NODE_ID)<2

My next question would be how to achieve the above - without returning the count(r.NODE_ID).  Reason - I'm building a view using this statement and it offends my sense of elegance to have the count column in the view.  A version I've come up with is using a derived table - but again it strikes me that there's probably a better way to do this:

 

select node from
(select distinct n.NODE, count(r.NODE_ID) from NODES n
join ROUTES r on n.ID=r.NODE_ID
where (r.ROUTE_INDEX=1 and (r.NETCON>5 or r.QUALITY>3)) or (r.ROUTE_INDEX=2 and (r.NETCON>5 or r.QUALITY>3))
group by n.NODE
union
SELECT n.NODE, COUNT(r.NODE_ID) FROM NODES n
join ROUTES r on n.ID=r.NODE_ID
GROUP BY n.NODE
HAVING COUNT(r.NODE_ID)<2) bad_routes (node, routecount)

 

--

Daniel