Problème de création de vue.

[Résolu] Problème de création de vue.

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:

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:

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:

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:

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:

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:

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:

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:

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 ?

Tu peux peut-être essayer :

Code:

1
2
3
4
5
6
7
8
 
select   *
from     LOCATION r
where    r.date in (
            select   max(s.date)
            from     LOCATION s
            where    s.experiment = r.experiment
         )

Laly.

merci bcp de m'avoir mis sur la voie .... jamais je n'aurais pensé à mettre le mot clé IN. D'ailleurs, quelle est la différence fondamentale ?

en tout cas, voici la requête qui renvoie le résultat escompté :
Code:

1 2 3 4 5 SELECT * FROM locations r WHERE r.date IN ( SELECT MAX(s.date) FROM locations s WHERE s.experiment = r.experiment AND s.user=r.user);

Honnêtement, sous Oracle j'aurais fais comme dans ta première requête avec le = (select ...).
Mais je me suis douté que le = (select ...) n'était pas supporté par ton SGBD.

Sous Oracle en tout cas, quand tu fais = (select ...) le select ne doit ramener qu'au plus une ligne. Alors que quand tu fais in (select ...) ca ne pose pas de pb si le select ramène plusieurs lignes, c'est même souvent le cas même si dans ton pb par construction on sait qu'il n'y aura qu'une seule ligne, c'était juste pour contourner cette contrainte.

Laly.