Discussion :

[CosmoKnacki]

Bonjour,
J'ai trois tables liées par des clefs étrangères à une quatrième:

Code SQL :

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
CREATE TABLE t_player(
    plr_id INT PRIMARY KEY,
    plr_name VARCHAR(30) NOT NULL,
    plr_fname VARCHAR(30) NOT NULL,
) ENGINE=InnoDB;
 
CREATE TABLE t_record(
    rec_id INT PRIMARY KEY AUTO_INCREMENT,
    rec_datetime DATETIME NOT NULL
) ENGINE=InnoDB;
 
CREATE TABLE t_condition(
    cdn_id INT PRIMARY KEY AUTO_INCREMENT,
    cdn_tsi INT NOT NULL,
) ENGINE=InnoDB;
 
CREATE TABLE t_history(
    fk_rec_id INT NOT NULL,
    fk_plr_id INT NOT NULL,
    fk_cdn_id INT NOT NULL,
 
    PRIMARY KEY (fk_plr_id, fk_rec_id),
 
    FOREIGN KEY (fk_rec_id) REFERENCES t_record(rec_id),
    FOREIGN KEY (fk_plr_id) REFERENCES t_player(plr_id),
    FOREIGN KEY (fk_cdn_id) REFERENCES t_condition(cdn_id)
) ENGINE=InnoDB;

Je souhaite obtenir pour chaque joueur (t_player), la liste des valeurs TSI (t_condition) pour toutes les dates d'enregistrement (t_record): ce qui implique des valeurs null pour les valeurs TSI à certaines dates où les joueurs sont partis ou pas encore arrivés dans l'équipe.
Le problème c'est qu'à empiler des jointures, ces valeurs null ont tendances à se faire la malle pour des raisons qui m'échappent.
Après avoir bien bataillé, j'ai fini par aboutir à ça (qui fonctionne parfaitement):

Code SQL :

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
WITH x AS (
  SELECT plr_id, plr_name, plr_fname,
         rec_id, rec_datetime
  FROM t_player
  CROSS JOIN t_record
) 
SELECT x.plr_name AS 'Name',
       x.plr_fname AS Forname,
       GROUP_CONCAT(COALESCE(c.cdn_tsi, '') ORDER BY x.rec_datetime) AS TSI
FROM t_history h
RIGHT JOIN x 
  ON x.plr_id = h.fk_plr_id
  AND x.rec_id = h.fk_rec_id
LEFT JOIN t_condition c
  ON h.fk_cdn_id = c.cdn_id
GROUP BY x.plr_id
ORDER BY x.plr_name, x.plr_fname

N'y a-t-il pas plus simple?

[Paraffine]

Je dirais, puisque tu souhaites une valeur pour chaque entraînement ; une requête UNION entre les entraînements où le joueur était présent et ceux où le joueur était absent ?

[escartefigue]

J'ai eu du mal à comprendre le besoin, je crois que ça y est

Du coup il me semble que ceci fera l'affaire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
   select PL.plr_id         
        , PL.plr_name
        , group_concat(COALESCE(CN.cdn_tsi, '') ORDER BY RC.rec_date) AS TSI
   FROM T_record RC           
   INNER JOIN T_history HI
      ON HI.fk_rec_id = RC.rec_id
   LEFT JOIN T_player   PL    
      ON PL.plr_id=HI.fk_plr_id
   LEFT JOIN T_condition  CN
      ON CN.CDN_id=HI.fk_cdn_id
   group by PL.plr_id         
          , PL.plr_name

[CosmoKnacki]

Merci pour vos réponses.

une requête UNION entre les entraînements où le joueur était présent et ceux où le joueur était absent ?

Je n'ai pas creusé cette piste, car je suis persuadé qu'on peut obtenir le résultat avec les bonnes jointures, mais c'est une idée, je ferai des essais.

J'ai eu du mal à comprendre le besoin, je crois que ça y est

Désolé, ce n'est pas encore ça. Avec cette requête on obtient bien toutes les valeurs TSI concaténées pour chaque joueur, mais tout le problème est que je souhaite également figurer dans cette concaténation les fois où le joueur n'est pas là (avec des chaînes vides pour représenter les valeurs TSI manquantes).

Prenons un exemple:

• Jacques est présent du début à la fin et a donc une valeur TSI pour chaque date
• Pierre est arrivé récemment et n'a pas de valeurs pour les premières dates
• Henri était présent dés le début, mais a quitté l'équipe, il n'a donc pas de valeurs TSI pour les dernières dates

Ta requête me donne:

Pierre	510, 625, 804
Jacques	1095, 1150, 1045, 1271, 1304, 1400
Henri	975, 900, 881, 860

Or moi ce que je veux obtenir c'est: (avec NA pour représenter les chaînes vides)

Pierre	NA, NA, NA, 510, 625, 804
Jacques	1095, 1150, 1045, 1271, 1304, 1400
Henri	975, 900, 881, 860, NA, NA

[Paraffine]

Il faut un produit joueur * date pour tous les cas où les joueurs sont absents, du coup je me demande comment on peut s'en sortir avec les jointures ?

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
 
SELECT plr.plr_name    AS 'Name',
           plr.plr_fname AS Forname,
           rec.date_time AS 'Date',
           cdn.cdn_tsi   AS TSI
 
       FROM t_history his
INNER JOIN t_player plr    ON plr.plr_id = his.fk_plr_id
INNER JOIN t_condition cdn ON  cdn.cdn_id = his.fk_cdn_id
INNER JOIN t_record rec   ON  rec.rec_id = his.fk_rec_id
 
UNION ALL
 
SELECT plr.plr_name   AS 'Name',
           plr.plr_fname AS Forname,
           rec.date_time AS 'Date',
           'NA' AS TSI
 
       FROM  t_player plr , 
                t_record  rec           
WHERE NOT EXISTS (SELECT 1 FROM t_history his
                             WHERE his.fk_rec_id = rec.rec_id
                                AND his.fk_plr_id = plr.plr_id)
 
ORDER BY plr.plr_name, plr.plr_fname, rec_date_time

Mais ce n'est pas équivalent à ta requête, cette liste-là est "à plat"=> il faudrait en faire une vue
Peut-être que tu vas garder ta formule ?

[CosmoKnacki]

Il faut un produit joueur * date pour tous les cas où les joueurs sont absents, du coup je me demande comment on peut s'en sortir avec les jointures ?

Bah dans ma première version, j'ai crée une sous requête avec le produit cartésien de t_player et t_record avec CROSS JOIN.

En partant de ton idée j'ai abouti à ([EDIT]J'avais oublier les parenthèses autour des tables du NATURAL JOIN[/EDIT])

Code SQL :

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
WITH j AS (
  SELECT his.fk_plr_id    AS plr_id,
         his.fk_rec_id    AS rec_id,
         cdn.cdn_tsi      AS TSI
  FROM t_history his
  INNER JOIN t_condition cdn
          ON cdn.cdn_id = his.fk_cdn_id
 
  UNION ALL
 
  SELECT plr.plr_id       AS plr_id,
         rec.rec_id       AS rec_id,
         'NA'             AS TSI
  FROM  t_player plr, t_record rec           
  WHERE NOT EXISTS (
    SELECT 1 FROM t_history his
    WHERE his.fk_rec_id = rec.rec_id
    AND   his.fk_plr_id = plr.plr_id
  )
)
SELECT p.plr_name AS 'Name',
       p.plr_fname AS Forname, 
       GROUP_CONCAT(j.TSI ORDER BY r.rec_datetime) AS TSI
FROM j
NATURAL JOIN (t_player p, t_record r)
GROUP BY j.plr_id
ORDER BY p.plr_name, p.plr_fname

pour obtenir le résultat voulu.
Je ne saurais dire si c'est plus ou moins "simple", ma question étant en ces termes, plutôt mal posée, mais quoi qu'il en soit, je te remercie car je ne suis pas mécontent d'avoir une autre manière de faire sous la main.

[CosmoKnacki]

Pas besoin de CROSS JOIN d'ailleurs un simple SELECT sur deux tables fait naturellement le produit cartésien:

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT p.plr_name AS 'Name',
       p.plr_fname AS Forname,
       GROUP_CONCAT(COALESCE(c.cdn_tsi, '') ORDER BY r.rec_datetime) AS TSI
FROM t_history h
RIGHT JOIN (SELECT plr_id, rec_id FROM t_player, t_record) AS x
  ON x.plr_id = h.fk_plr_id
  AND x.rec_id = h.fk_rec_id
NATURAL JOIN t_record r
LEFT JOIN t_condition c
  ON h.fk_cdn_id = c.cdn_id
NATURAL JOIN t_player p
GROUP BY p.plr_id
ORDER BY p.plr_name, p.plr_fname

il faudrait en faire une vue

Quelque soit la solution retenue (au passage toute suggestion est la bienvenue), ça me semble être une bonne idée, car peu de mises à jour.

[escartefigue]

Pas besoin de CROSS JOIN d'ailleurs un simple SELECT sur deux tables fait naturellement le produit cartésien:

oui, ces 3 formulations produisent le même résultat

FROM T1 CROSS JOIN T2.

FROM T1 INNER JOIN T2 ON 1=1.

FROM TI, T2.

Mais l'absence de critères de jointure est moins lisible et peut passer pour un oubli, un produit cartésien explicite a le mérite d'être clair

[CosmoKnacki]

Ayé

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT p.plr_name AS 'Name',
       p.plr_fname AS Forname,
       GROUP_CONCAT(COALESCE(c.cdn_tsi, '') ORDER BY r.rec_datetime) AS TSI
FROM (t_player p, t_record r)
LEFT JOIN (t_history h, t_condition c)
  ON  h.fk_plr_id = p.plr_id 
  AND h.fk_rec_id = r.rec_id
  AND h.fk_cdn_id = c.cdn_id
GROUP BY p.plr_id
ORDER BY p.plr_name, p.plr_fname

ou dans sa version plus explicite:

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT p.plr_name AS 'Name',
       p.plr_fname AS Forname,
       GROUP_CONCAT(COALESCE(c.cdn_tsi, '') ORDER BY r.rec_datetime) AS TSI
FROM t_player p
CROSS JOIN t_record r
LEFT JOIN t_history h
  ON  h.fk_plr_id = p.plr_id 
  AND h.fk_rec_id = r.rec_id
LEFT JOIN t_condition c
  ON  h.fk_cdn_id = c.cdn_id
GROUP BY p.plr_id
ORDER BY p.plr_name, p.plr_fname