I am modeling some abstract flow tests.
A 'flow instance' is defined as some sort of flow from Node A to Node B.
This is simply modeled with 3 tables as follows (I have left out the
alter table add constraints for brevity):
CREATE TABLE FLOW
(
ID integer NOT NULL PRIMARY KEY,
NAME varchar(20),
);
CREATE TABLE NODE
(
ID integer NOT NULL PRIMARY KEY,
NAME varchar(20),
);
CREATE TABLE J_FLOW_INSTANCE
(
ID integer NOT NULL PRIMARY KEY,
FLOW integer, -- FOREIGN KEY (FLOW) REFERENCES FLOW (ID)
SRC integer, -- FOREIGN KEY (SRC) REFERENCES NODE (ID)
DEST integer -- FOREIGN KEY (DEST) REFERENCES NODE (ID)
);
Each test represents a flow instance with a test mode:
CREATE TABLE MODE
(
ID integer NOT NULL PRIMARY KEY,
NAME varchar(20),
);
CREATE TABLE J_FLOW_TEST
(
ID integer NOT NULL PRIMARY KEY,
INSTANCE integer, -- FOREIGN KEY (INSTANCE) REFERENCES
J_FLOW_INSTANCE (ID)
MODE integer -- FOREIGN KEY (MODE) REFERENCES MODE (ID)
);
The results of each test are measured in terms of multiple
characteristics, i.e. High Flow, Low Loss or High Flow, High Loss etc.
CREATE TABLE CHARACTERISTIC
(
ID integer NOT NULL PRIMARY KEY,
NAME varchar(20)
);
CREATE TABLE J_TEST_RESULT_CHARS
(
TEST integer, -- FOREIGN KEY (TEST) REFERENCES J_FLOW_TEST (ID)
RESULT integer -- FOREIGN KEY (RESULT) REFERENCES CHARACTERISTIC (ID)
);
With characteristics being defined by multiple properties:
CREATE TABLE PROPERTY
(
ID integer NOT NULL PRIMARY KEY,
NAME varchar(20)
);
CREATE TABLE J_CHAR_PROPERTY
(
CHARACTERISTIC integer, -- FOREIGN KEY (CHARACTERISTIC) REFERENCES
CHARACTERISTIC (ID)
PROPERTY integer -- FOREIGN KEY (PROPERTY) REFERENCES PROPERTY (ID)
);
I can query the results perfectly using the following query:
select jft.ID as TEST_ID, JFI_ID, F_NAME, N1_NAME, N2_NAME, m.NAME as
MODE, rc.NAME as CHARACTERISTIC, p.NAME as PROPERTY
from J_FLOW_TEST jft
join
(
select jfi.ID as JFI_ID, f.NAME as F_NAME, n1.NAME as N1_NAME,
n2.NAME as N2_NAME
from J_FLOW_INSTANCE jfi
join FLOW f on f.ID = FLOW
join NODE n1 on n1.ID = SRC
join NODE n2 on n2.ID = DEST
)
on JFI_ID = jft.INSTANCE
join MODE m on m.ID = jft.MODE
join J_TEST_RESULT_CHARS jtrc on jtrc.TEST = jft.ID
join CHARACTERISTIC rc on rc.ID = jtrc.RESULT
join J_CHAR_PROPERTY jcp on jcp.CHARACTERISTIC = rc.ID
join PROPERTY p on p.ID = jcp.PROPERTY
order by TEST_ID, F_NAME, N1_NAME, N2_NAME, MODE, CHARACTERISTIC, PROPERTY
Which gives me results like this:
_TEST_ID JFI_ID F_NAME N1_NAME N2_NAME MODE
CHARACTERISTIC PROPERTY_
1 1 FLOW_1 NODE_A NODE_B MODE_1 HIGH FLOW > 10 litres
/ sec
1 1 FLOW_1 NODE_A NODE_B MODE_1 HIGH FLOW > 50 gals / hour
1 1 FLOW_1 NODE_A NODE_B MODE_1 LOW LOSS Pump current < 30
2 2 FLOW_2 NODE_C NODE_D MODE_1 LOW FLOW < 5 litres / sec
44 3 FLOW_1 NODE_C NODE_D MODE_2 LOW FLOW < 5 litres / sec
I have 2 questions:
Q1. How can I group and LIST() to produce a single row for the first
test case (FLOW_1 from NODE_A to NODE_B with MODE_1). The result would
look something like this:
1 1 FLOW_1 NODE_A NODE_B MODE_1 HIGH_FLOW, LOW_LOSS >
10 litres / sec, > 50 gals / hour, Pump current < 30
Q2. How can I create a further table J_RESULT_GROUP and query for
arbitrarily grouping results together where any distinct elements are
LIST()ed whilst common elements are 'GROUP'ed (i.e. appear only once).
For example if J_RESULT_GROUP had 2 rows:
_GROUP TEST_
1 2
1 44
It would give:
_TEST_ID JFI_ID F_NAME N1_NAME N2_NAME MODE
CHARACTERISTIC PROPERTY
_2,44 2,3 FLOW_1, FLOW_2 NODE_C NODE_D MODE_1, MODE_2
LOW_FLOW < 5 litres / sec
I am sorry for the long question, but I have struggled to phrase it in
simple terms.
TIA for any help!
