Subject select parent based on multiple child conditions
Author Daniel Miller
Post date 2015-12-19T06:43:02Z
I'm not sure how to construct this.  There's probably an elegant solution available - I'm trying to feel my way there but...
 
Two tables.
 
CREATE TABLE NODES
(
  ID Smallint NOT NULL,
  NODE Smallint NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE (NODE)
);
 
CREATE TABLE ROUTES
(
  ID Integer NOT NULL,
  NODE_ID Smallint NOT NULL,
  ROUTE_INDEX Smallint NOT NULL,
  QUALITY Smallint NOT NULL,
  PRIMARY KEY (ID)
);
ALTER TABLE ROUTES ADD CONSTRAINT FK_ROUTE_NODE_ID
  FOREIGN KEY (NODE_ID) REFERENCES NODES (ID) ON UPDATE CASCADE ON DELETE CASCADE;
Table "Nodes" is simply a unique list, and table "Routes" has multiple children.  If it makes a difference for performance considerations, "Nodes" can potentially grow to 10,000 entries, and up to 8 Routes per Node.  Route_Index will be sequentially numbered for entries of each Node_ID.
 
I want to obtain a result set of Nodes that represent problems.  "Good" Nodes have at least two routes, and the first two routes must have Quality>7.  So "Bad" routes are any of the following:
 
1.  Less than 2 Routes per node.
2.  Route_Index=1 has Quality<7
3.  Route_Index=1 has Quality<7
 
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
 
This appears to give me a valid result set for my purposes.  The (r.QUALITY>7) constructs are in parentheses because I've simplified the tables and there are additional tests being done to determine validity within the parens.
 
So my question is - is there a "better" way of accomplishing this?
--
Daniel