IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Langage SQL Discussion :

Requête sur plusieurs tables


Sujet :

Langage SQL

  1. #1
    Nouveau Candidat au Club
    Homme Profil pro
    Développeur en systèmes embarqués
    Inscrit en
    mai 2022
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur en systèmes embarqués

    Informations forums :
    Inscription : mai 2022
    Messages : 5
    Points : 1
    Points
    1
    Par défaut Requête sur plusieurs tables
    Bonjour à tous,

    J'ai une BDD Mysql contenant 3 tables : Joueurs, Trajets et Jeux.
    La description des tables est en pièce jointe.

    Je souhaiterai obtenir un comptage du nombre de jeux pour chaque joueur et pour chaque type de trajet, par jour, même si un joueur n'a pas effectué tous les types de trajets pour cette journée.
    Ma table Joueurs contient 6 joueurs; ma table Trajets contient 6 types de trajets. Ma requête doit donc retourner 6 x 6 = 36 lignes.
    La requête suivante sort un comptage uniquement pour les types de trajets qui ont été effectués dans la journée.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    select A.Joueur, A.NbTrajet, count(*) from Jeux A left join Trajets B on A.NbTrajet = B.NBTrajet where Date='2022-05-10' group by A.Joueur, A.NbTrajet order by A.Joueur;
    Pourriez-vous m'aider svp ?

    Merci par avance.

    Cdt
    Patrick
    Images attachées Images attachées  

  2. #2
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    8 392
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 8 392
    Points : 30 232
    Points
    30 232
    Billets dans le blog
    2
    Par défaut
    bonjour,

    Si on veut toutes les dates d'une période (par exemple d'une année), même celles où certains joueurs (voir aucun joueur) n'a joué, alors il faut créer une table calendrier contenant toutes les dates correspondantes et faire une requête sur cette table en interne (table INNER) et les autres en externe (OUTER JOIN)

    Pour le reste, le modèle de données est très mal fait : dans la table "jeux", on trouve une colonne foreign key qui n'est pas primary key d'une autre table
    C'est la colonne "idtrajet" qu'on devrait trouver dans jeux et non pas "nbtrajet".

    De plus, il semble que cette table "jeux" soit une table associative entre "joueurs" et "trajets". Si c'est bien le cas, cette table ne devrait pas avoir d'identifiant propre (idjeu), sa PK devrait être composé des PK des tables "joueurs" et "trajets"

    Egalement, la présence de 10 colonnes "durée1" à "durée10" est suspecte, le plus souvent, ce type de colonnes répétées est une erreur de modélisation
    Seul cas où cette modélisation est correcte : il y a toujours obligatoirement 10 et seulement 10 durées, et ça ne changera pas.
    Et si la colonne "durée totale" représente la somme des 10 autres, alors c'est une redondance à supprimer

    Point supplémentaire "date" est un mot réservé SQL, à éviter comme nom d'objet (table, colonne...), car ça complique inutilement les requêtes (délimiteurs obligatoires) et complique les analyses.

    Enfin, l'usage est de nommer les tables au singulier, par exemple "joueur" plutôt que "joueurs", il est évident que la table contiendra plusieurs occurrences de joueurs

    Tout ceci étant dit, la requête suivante fait le comptage du nombre de trajets par date de chaque joueur :

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    select JE.dttrajet
         , JO.prenom
         , count(distinct JE.idtrajet)
    from jeux as JE     
    left join joueurs as JO
       on JO.idjoueur=JE.idjoueur
    group by JE.dttrajet
           , JO.prenom   
    ;

    EDIT : j'oubliais, il est demandé de compter le nombre de "types de trajets", mais cette notion n'apparait pas dans les tables, c'est pourquoi j'ai compté les identifiants de trajets dans la requête ci-dessus. Merci d'indiquer comment on identifie un "type de trajet"

  3. #3
    Nouveau Candidat au Club
    Homme Profil pro
    Développeur en systèmes embarqués
    Inscrit en
    mai 2022
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur en systèmes embarqués

    Informations forums :
    Inscription : mai 2022
    Messages : 5
    Points : 1
    Points
    1
    Par défaut
    Bonjour,

    Merci pour votre réponse et vos remarques pertinentes.
    Je n'ai peut-être pas été suffisamment clair dans mes explications.

    Pour le "type de trajets", il s'agit de NbTrajet (les valeurs possibles sont 1, 2, 3, 4, 5 et 10).
    Un "jeu" est la réalisation d'un type de trajets (donc 1, 2, ... ou 10 trajets). La durée de chaque trajet étant chronométrée.

    En fait, la requête ne répond pas complètement à mon besoin. Je voudrais pour l'ensemble des joueurs et des types de trajets, le nombre de jeux effectués (qui peut valoir 0 ou tout autre valeur), pour une date donnée.
    Est-ce possible ?

    PS : je reconstruirai le modèle de données en suivant vos conseils.

    Cdt
    Patrick

  4. #4
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    8 392
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 8 392
    Points : 30 232
    Points
    30 232
    Billets dans le blog
    2
    Par défaut
    Il est peu utile (sauf à titre didactique) de mettre au point la requête puisque le modèle de données doit être revu.

    Pour votre besoin, il faut donc comme je le suggérai plus haut créer une table calendrier avec toutes les dates de la période qui vous intéresse.
    Un moyen simple de créer ce type de tables est d'utiliser une requête récursive si votre version MariaDB vous le permet.

    Voici la création d'un jeu d'essai (avec les tables créées à minima, je n'ai pas créé la table "trajets" inutile ici) :

    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
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    create table Calend
          (  CAdate   date not null)
    ;
    insert into Calend (CAdate)
    values ('2021-01-01'), ('2021-01-02'), ('2021-01-03')
         , ('2021-01-04'), ('2021-01-05'), ('2021-01-06')
         , ('2021-01-07'), ('2021-01-08'), ('2021-01-09')
         , ('2021-01-10'), ('2021-01-11'), ('2021-01-12')     
    ;
    create table Joueurs
          (  IDJoueur      integer auto_increment  primary key
           , PrenomJoueur  varchar(50)
          )
    ;
    insert into Joueurs(PrenomJoueur)
    values ('Benoit'), ('Esmeralda'), ('Mouloud'), ('Sergeï')
    ;
    create table Jeux
          (  IDjeu        integer auto_increment  primary key
           , IDjoueur     integer      not null
           , NbTrajet     smallint     not null
           , Jdate        date         not null
          )
    ;
    insert into Jeux (IDjoueur, NbTrajet, Jdate)
    values (01, 01, '2021-01-02')
         , (01, 10, '2021-01-02')
         , (01, 03, '2021-01-10')
         , (01, 04, '2021-01-12')
         , (02, 02, '2021-01-03')
         , (02, 03, '2021-01-04')
         , (02, 05, '2021-01-04')
         , (02, 06, '2021-01-06')
         , (03, 01, '2021-01-06')
         , (03, 03, '2021-01-07')
         , (03, 03, '2021-01-09')
         , (04, 04, '2021-01-02')
         , (04, 10, '2021-01-06')
    ;

    La requête :
    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 CA.CAdate
            , JO.Prenom
            , count(JE.NbTrajet)
       from Calend as CA
       left join Jeux as JE
          on JE.Jdate=CA.CAdate
       left join Joueurs as JO
          on JO.IDjoueur=JE.IDjoueur
       group by CA.CAdate
              , JO.prenom
       order by CA.CAdate
              , JO.prenom
       ;

    Et le résultat :
    CAdate IDjoueur PrenomJoueur Nbr
    2021-01-01 null 0 null
    2021-01-02 1 Benoit 2
    2021-01-02 4 Sergeï 1
    2021-01-03 2 Esmeralda 1
    2021-01-04 2 Esmeralda 2
    2021-01-05 null 0 null
    2021-01-06 2 Esmeralda 1
    2021-01-06 3 Mouloud 1
    2021-01-06 4 Sergeï 1
    2021-01-07 3 Mouloud 1
    2021-01-08 null 0 null
    2021-01-09 3 Mouloud 1
    2021-01-10 1 Benoit 1
    2021-01-11 null 0 null
    2021-01-12 1 Benoit 1

    Grâce à la table "calendrier", on a bien toutes les dates, même celles qui n'ont été jouées par aucun joueur.

  5. #5
    Nouveau Candidat au Club
    Homme Profil pro
    Développeur en systèmes embarqués
    Inscrit en
    mai 2022
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur en systèmes embarqués

    Informations forums :
    Inscription : mai 2022
    Messages : 5
    Points : 1
    Points
    1
    Par défaut
    Bonjour,

    Merci pour vos tests.
    Je vais reconstruire le modèle de données. Je vous tiendrai au courant.

    Cdt
    Patrick

  6. #6
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    8 392
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 8 392
    Points : 30 232
    Points
    30 232
    Billets dans le blog
    2
    Par défaut
    Pour bien concevoir le modèle de données, il est recommandé de collecter les règles de gestion, puis de commencer par le modèle conceptuel (MCD).
    Il y a un forum consacré à la modélisation, il se trouve ICI

  7. #7
    Nouveau Candidat au Club
    Homme Profil pro
    Développeur en systèmes embarqués
    Inscrit en
    mai 2022
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur en systèmes embarqués

    Informations forums :
    Inscription : mai 2022
    Messages : 5
    Points : 1
    Points
    1
    Par défaut
    J'ai recréé mes tables comme suit :
    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
    CREATE TABLE `Joueur` (
      `IDJoueur` smallint(2) NOT NULL,
      `Prenom` varchar(50) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Liste des joueurs';
    ALTER TABLE `Joueur`
      ADD PRIMARY KEY (`IDJoueur`);
    CREATE TABLE `Trajet` (
      `IDTrajet` smallint(2) NOT NULL,
      `NBTrajet` smallint(2) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Liste du nombre de trajets possibles pour un jeu';
    ALTER TABLE `Trajet`
      ADD PRIMARY KEY (`IDTrajet`);
    CREATE TABLE `Jeu` (
      `IDJoueur` smallint(2) NOT NULL,
      `IDTrajet` smallint(2) NOT NULL,
      `Jdate` date NOT NULL COMMENT 'Date du jeu',
      `Jheure` time NOT NULL COMMENT 'Heure du jeu',
      `Duree1` decimal(5,2) NOT NULL,
      `Duree2` decimal(5,2) DEFAULT NULL,
      `Duree3` decimal(5,2) DEFAULT NULL,
      `Duree4` decimal(5,2) DEFAULT NULL,
      `Duree5` decimal(5,2) DEFAULT NULL,
      `Duree6` decimal(5,2) DEFAULT NULL,
      `Duree7` decimal(5,2) DEFAULT NULL,
      `Duree8` decimal(5,2) DEFAULT NULL,
      `Duree9` decimal(5,2) DEFAULT NULL,
      `Duree10` decimal(5,2) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Liste des jeux effectués par les joueurs';
    ALTER TABLE `Jeu`
      ADD KEY `fk_Joueur_IDJoueur` (`IDJoueur`),
      ADD KEY `fk_Trajet_IDTrajet` (`IDTrajet`);
    ALTER TABLE `Jeu`
      ADD CONSTRAINT `fk_Joueur_IDJoueur` FOREIGN KEY (`IDJoueur`) REFERENCES `Joueur` (`IDJoueur`),
      ADD CONSTRAINT `fk_Trajet_IDTrajet` FOREIGN KEY (`IDTrajet`) REFERENCES `Trajet` (`IDTrajet`);
    Avec les données suivantes (pour l'instant) :

    Nom : Select.JPG
Affichages : 35
Taille : 88,7 Ko

    Je pense que je me suis mal exprimé.

    En fait, je souhaiterai obtenir en sortie les résultats ci-dessous, en effectuant une sélection sur la date 2022-05-12 (pour l'exemple), donc avec un where Jdate='2022-05-12' :

    Nom : TableauSortie.JPG
Affichages : 35
Taille : 33,8 Ko

    Si l'on prend la ligne 6 du tableau : cela signifie que Patrick a effectué 2 jeux de 2 trajets pour la journée du 2022-05-12.
    Pour la ligne 13 : cela signifie qu' Estelle a effectué 0 jeu de 5 trajets pour la journée du 2022-05-12.
    Pour la journée du 2022-05-12 on a bien au total 6 jeux qui ont été effectués, avec des nombres de trajets différents selon les jeux.

    Est-ce que l'on peut faire cela avec des "join" ?

    NB : les durées ne sont pas utilisées dans cette requête; elles le seront dans une autre requête ultérieure.

  8. #8
    Membre chevronné
    Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    juin 2019
    Messages
    514
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 59
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Professeur des Universités
    Secteur : Enseignement

    Informations forums :
    Inscription : juin 2019
    Messages : 514
    Points : 2 045
    Points
    2 045
    Par défaut
    Bonsoir,

    Je vois deux problèmes à la structure de votre base de données :
    • La table "Jeu" ne possède pas de clé primaire comme le montrent les 2 "date-heure" équivalentes sur 2 enregistrements ; déjà, je ne comprends pas que le SGBD tolère ça, mais bon...
    • Les 10 rubriques "Durée" dans la table "Jeu" génèrent des tas de valeurs NULL, ce qui ferait bondir tout concepteur de BD respectueux des formes normales !


    Bref, pour moi, la clé primaire de "Jeu" doit être composée de l'identifiant du joueur, de la date et de l'heure du jeu, ainsi que d'un numéro de durée : il y aura alors autant d'enregistrements qu'il y a de durées, ce qui ouvrira de nombreuses possibilités en termes de requêtes.

    Et surtout, comme le recommande Escartefigue, il est toujours préférable de commencer par la modélisation conceptuelle avant de s'attaquer directement à SQL.

    Voici le MCD, le MLD et DDL qui découleraient de la prise en compte ces remarques :
    Nom : MCD Patrick_38.jpg
Affichages : 34
Taille : 18,2 Ko
    Nom : MLD Patrick_38.jpg
Affichages : 34
Taille : 13,0 Ko
    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
    CREATE TABLE Joueur(
       Id_Joueur SMALLINT,
       Prénom VARCHAR(50),
       PRIMARY KEY(Id_Joueur)
    );
     
    CREATE TABLE Trajet(
       Id_Trajet SMALLINT,
       NbTrajet INT,
       PRIMARY KEY(Id_Trajet)
    );
     
    CREATE TABLE Jeu(
       Id_Joueur SMALLINT,
       Jdate DATE,
       Jheure TIME,
       NumDurée TINYINT,
       Durée DECIMAL(5,2),
       Id_Trajet SMALLINT NOT NULL,
       PRIMARY KEY(Id_Joueur, Jdate, Jheure, NumDurée),
       FOREIGN KEY(Id_Joueur) REFERENCES Joueur(Id_Joueur),
       FOREIGN KEY(Id_Trajet) REFERENCES Trajet(Id_Trajet)
    );
    Bonne continuation !
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  9. #9
    Nouveau Candidat au Club
    Homme Profil pro
    Développeur en systèmes embarqués
    Inscrit en
    mai 2022
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur en systèmes embarqués

    Informations forums :
    Inscription : mai 2022
    Messages : 5
    Points : 1
    Points
    1
    Par défaut
    Bonjour,

    OK, merci pour les conseils et les modèles fournis. Je vais recréer la BDD.
    Par contre, pour l'instant, je ne vois pas trop comment je vais récupérer les infos dont j'ai besoin mais je vais creuser et reviendrai vers vous 2 si besoin. Merci encore messieurs.

    Cdt
    Patrick

Discussions similaires

  1. suite au problème de requête sur plusieur table
    Par michelGProuq dans le forum Requêtes et SQL.
    Réponses: 1
    Dernier message: 07/07/2006, 16h19
  2. [VB6] RecordSet, Oracle, requête sur plusieurs Tables
    Par pier* dans le forum VB 6 et antérieur
    Réponses: 5
    Dernier message: 13/04/2006, 10h19
  3. Requête sur plusieurs tables
    Par sta_schmitt dans le forum Requêtes
    Réponses: 2
    Dernier message: 28/03/2006, 13h54
  4. Requéte sur plusieurs tables
    Par polux23 dans le forum Requêtes
    Réponses: 11
    Dernier message: 23/02/2006, 23h00
  5. Requête sur plusieurs tables
    Par drinkmilk dans le forum Langage SQL
    Réponses: 8
    Dernier message: 11/07/2005, 12h25

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo