Re: [firebird-support] select parent based on multiple child conditions

 

 

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

__._,_.___

---

Posted by: "Daniel Miller" <dmiller@amfes.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___