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
