create table I2 ( /* main table */
ID integer not null primary key,
INST varchar(32) not null unique
);
create table A2 ( /* alias table */
ALI varchar(32) not null unique,
ID integer not null references I2(ID) on update cascade on delete cascade
);
create table D2 ( /* data table */
ID integer not null,
YMD date not null,
TB integer,
MB integer,
DB integer,
primary key(ID, YMD), /* is this ascending? if so, how to make it descending? */
foreign key(ID) references I2(ID) on update cascade on delete cascade
);
insert into i2(id,inst) values(1,'s0001');
insert into i2(id,inst) values(2,'s0002');
insert into i2(id,inst) values(3,'s0003');
insert into i2(id,inst) values(4,'s0004');
insert into i2(id,inst) values(5,'s0005');
insert into a2(ali,id) values('a0002',2);
insert into a2(ali,id) values('a0005',5);
insert into d2(id,ymd,tb,mb,db) values(1, '2011-12-21',11,12,13);
insert into d2(id,ymd,tb,mb,db) values(1, '2011-12-22',21,22,23);
insert into d2(id,ymd,tb,mb,db) values(1, '2011-12-23',31,32,33);
insert into d2(id,ymd,tb,mb,db) values(1, '2011-12-26',61,62,63);
insert into d2(id,ymd,tb,mb,db) values(2, '2011-12-21',11,12,13);
insert into d2(id,ymd,tb,mb,db) values(2, '2011-12-22',21,22,23);
insert into d2(id,ymd,tb,mb,db) values(2, '2011-12-23',31,32,33);
insert into d2(id,ymd,tb,mb,db) values(2, '2011-12-26',61,62,63);
insert into d2(id,ymd,tb,mb,db) values(3, '2011-12-21',11,12,13);
insert into d2(id,ymd,tb,mb,db) values(3, '2011-12-22',21,22,23);
insert into d2(id,ymd,tb,mb,db) values(3, '2011-12-23',31,32,33);
insert into d2(id,ymd,tb,mb,db) values(3, '2011-12-26',61,62,63);
insert into d2(id,ymd,tb,mb,db) values(4, '2011-12-21',11,12,13);
insert into d2(id,ymd,tb,mb,db) values(4, '2011-12-22',21,22,23);
insert into d2(id,ymd,tb,mb,db) values(4, '2011-12-23',31,32,33);
insert into d2(id,ymd,tb,mb,db) values(4, '2011-12-26',61,62,63);
insert into d2(id,ymd,tb,mb,db) values(5, '2011-12-21',11,12,13);
insert into d2(id,ymd,tb,mb,db) values(5, '2011-12-22',21,22,23);
insert into d2(id,ymd,tb,mb,db) values(5, '2011-12-23',31,32,33);
insert into d2(id,ymd,tb,mb,db) values(5, '2011-12-26',61,62,63);
For an INST in I2, there can be zero or more aliases in A2.
For an ALI value in A2, there must exist an INST value in I2.
The reverse is not necessarily true.
I would like to extract tb,mb,db from d2 corresponding to both a string and
date. For example, ('s0001' and '2011-12-21') or ('a0002' and '2011-12-21').
The string can be either any one of INST in I2 or any one of ALI in A2. If
it's an ALI from A2, first the matching INST from I2 must be found.
SELECT A2.ID FROM A2 JOIN I2 ON A2.ID = I2.ID WHERE A2.ALI = 'a0002'; /*
returns 2 from A2*/
SELECT D2.ID, D2.YMD, D2.TB, D2.MB, D2.DB FROM D2 WHERE D2.ID = A2.ID AND
D2.YMD = '2011-12-21'; /* return 2 from D2 */
SELECT D2.ID, D2.YMD, D2.TB, D2.MB, D2.DB FROM D2 JOIN I2 ON D2.ID = I2.ID
WHERE I2.ID = 2 AND D2.YMD = '2011-12-21';
I experimented with these separate steps and worked fine.
Now, I would like to create a single SELECT statement doing all these steps if
possible.
Can someone write this for me please?
thx a lot
HR
