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(
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,