Bonjour à tous,
Je continue mon apprentissage (dans la douleur) de SQL.

Je travaille en SQL2, sous HSQLDB.

Voici les tables que je crée :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
 
CREATE TABLE users(
 id INTEGER IDENTITY, //équivalent à autoincrement PK
 login VARCHAR(50),
 password VARCHAR(20),
 name VARCHAR(50),
 gender VARCHAR(6),
 preferred_language VARCHAR(2),
 profile VARCHAR(7),
 preferred_character VARCHAR(10),
 CONSTRAINT uniq_users UNIQUE (login),
 CONSTRAINT valid_users_character CHECK(preferred_character IN ('mayday','frog')),
 CONSTRAINT valid_users_profile CHECK(profile IN ('student','admin')),
 CONSTRAINT valid_users_gender CHECK(gender IN ('male','female')));
 
CREATE TABLE sessions(
 id INTEGER IDENTITY,
 user INTEGER,
 date DOUBLE,
 duration DOUBLE,
 CONSTRAINT fk_sessions_users FOREIGN KEY (user) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT valid_sessions_date CHECK(date>0),
 CONSTRAINT valid_sessions_duration CHECK(duration>=0)
);
 
CREATE TABLE tentatives(
 id INTEGER IDENTITY,
 session INTEGER,
 experiment VARCHAR(10),
 date DOUBLE,
 duration DOUBLE,
 score DOUBLE,
 status VARCHAR(10),
 location VARCHAR(255),
 CONSTRAINT fk_tentatives_sessions FOREIGN KEY (session) REFERENCES sessions(id) ON DELETE SET NULL,
 CONSTRAINT valid_score CHECK(score>=0 AND score<=100),
 CONSTRAINT valid_tentatives_date CHECK(date>0),
 CONSTRAINT valid_tentatives_duration CHECK(duration>=0),
 CONSTRAINT valid_tentatives_status CHECK(status IN ('passed','completed','failed','incomplete','browsed'))
);
Ce que je veux obtenir c'est :
pour chaque user, obtenir tous les experiment, avec la location la plus récente lui correspondant.

Voici l'état de mes tables :
USERS
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
 
ID  LOGIN        PASSWORD   NAME          GENDER  PREFERRED_LANGUAGE  PROFILE  PREFERRED_CHARACTER
--  -----------  ---------  ------------  ------  ------------------  -------  -------------------
 0  login        mdp        nom           male    fr                  student  mayday
 3  login2       password2  christine-2   female  es                  admin    frog
 5  log          password2  edouard baer  male    no                  admin    mayday
 9  miaou        password2  edouard baer  male    no                  admin    mayday
10  miaou22      password2  edouard baer  male    no                  admin    mayday
12  miaou223     password2  edouard baer  male    no                  admin    mayday
15  miaou2234    password2  edouard baer  male    no                  admin    mayday
18  miaou22345   password2  edouard baer  male    no                  admin    mayday
19  miaou223456  password2  edouard baer  male    no                  admin    mayday
SESSIONS
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
 
ID  USER  DATE          DURATION
--  ----  ------------  --------
 0     0  10.0          26568.0
 1     0  100.0         26568.0
 2     0  1245.0        26568.0
14     9  1451212.0     45789.0
15     9  2457889.0     89.0
16     9  35.0          89457.0
20     3  458.0         45693.0
21     3  458458.0      45693.0
22     3  1.0           45693.0
23    10  7.89454875E8  1256.0
TENTATIVES
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
ID  SESSION  EXPERIMENT  DATE           DURATION  SCORE  STATUS     LOCATION
--  -------  ----------  -------------  --------  -----  ---------  ------------
 
 1        2  a12         10.0           12.0      75.8   passed     location
 2        2  b1          45687.0        12.0      75.8   passed     location2
 3       21  c125        1054.0         12.0      75.8   passed     location-3
 4       15  n7          1054792.0      12.0      75.8   passed     location4
 5       23  d4          8.5693215E7    1248.0    20.63  failed     ici
 6       16  p26         4.5987613E7    12345.0   1.26   completed  maLocation
 7       14  p26         1.245987613E9  12345.0   1.26   failed     maLocation22
 
 8        2  p26         7712459.0      7812.0    58.3   completed  maLoc
 9        2  p27         124583.0       7812.0    58.3   completed  parla
Voici donc le résultat auquel je voudrais parvenir :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
 
USER  SESSION  EXPERIMENT  LOCATION      DATE
----  -------  ----------  ------------  -------------
   0        2  a12         location      10.0
   0        2  b1          location2     45687.0
   0        2  p26         maLoc         7712459.0
   0        2  p27         parla         124583.0
   9       14  p26         maLocation22  1.245987613E9
   9       15  n7          location4     1054792.0
   3       21  c125        location-3    1054.0
  10       23  d4          ici           8.5693215E7
Pour le moment, le meilleur résultat auquel je parviens est le suivant : (vue nommée "locations")
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
 
USER  SESSION  EXPERIMENT  LOCATION      DATE
----  -------  ----------  ------------  -------------
   0        2  a12         location      10.0
   0        2  b1          location2     45687.0
   0        2  p26         maLoc         7712459.0
   0        2  p27         parla         124583.0
   9       14  p26         maLocation22  1.245987613E9
   9       15  n7          location4     1054792.0
   9       16  p26         maLocation    4.5987613E7 <-- la ligne en trop !
   3       21  c125        location-3    1054.0
  10       23  d4          ici           8.5693215E7
Pour cela, je crée la vue tq :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
 
CREATE VIEW locations AS
    SELECT r.user AS user, t.session AS session, t.experiment AS experiment, t.location AS location, t.date AS date FROM sessions r 
        INNER JOIN tentatives t ON t.session=r.id;
Théoriquement, si je faisais :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
SELECT * FROM locations r WHERE r.date=(SELECT MAX(s.date) FROM locations s WHERE s.experiment=r.experiment);
je devrais m'en sortir.

le problème est que HSQLDB ne veut pas de cette dernière requête.

qqn aurait une idée ?