Discussion :

[Equinoxe58]

Bonjour,
J'ai une requête qui ne me donne pas le résultat que je souhaite. Ca veut dire qu'elle est mal écrite, mais je ne vois pas comment faire.
Voici la requête :

Code :

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

SELECT t1.id_identite, t1.nom, t1.prenom, t1.surnom, t1.id_photo, t1.numero, t2.id_photo, t2.nom_photo, t2.nom_clair, t2.en_ligne FROM photo_identite AS t1, photo_photo AS t2 WHERE t2.en_ligne = '0' AND t1.id_photo = t2.id_photo AND t1.prenom LIKE '%bou%' OR t1.nom LIKE '%bou%' GROUP BY t1.nom, t1.prenom

Et voici le schéma des tables :

Code :

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

1
2
3
4
5
6
7
8
9
 
CREATE TABLE IF NOT EXISTS `photo_photo` (
  `id_photo` int NOT NULL AUTO_INCREMENT,
  `nom_photo` varchar(32) NOT NULL,
  `nom_clair` varchar(32) NOT NULL,
  `annee` int NOT NULL,
  `en_ligne` int NOT NULL,
  PRIMARY KEY (`id_photo`)
) ENGINE=MyISAM AUTO_INCREMENT=98 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

et

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS `photo_identite` (
  `id_identite` int NOT NULL AUTO_INCREMENT,
  `nom` varchar(32) NOT NULL,
  `prenom` varchar(32) NOT NULL,
  `surnom` varchar(32) NOT NULL,
  `genre` varchar(3) NOT NULL,
  `id_photo` tinyint NOT NULL,
  `numero` tinyint NOT NULL,
  PRIMARY KEY (`id_identite`)
) ENGINE=InnoDB AUTO_INCREMENT=2276 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Je fais une recherche par nom ou prénom par autocomplétion, avec un résultat QUE SI la photo est en ligne. Et voici le résultat obtenu :



Je précise que sur la dernière ligne de ma copie d'écran, le champ en_ligne de la seconde table est à 0
Qui peut m'aider pour la requête correcte svp ?

Merci par avance

[tatayo]

Bonjour,
Je me permets de "mettre au propre" la requête, pour plus de clareté:

Code sql :

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

1
2
3
4
5
6
 
SELECT t1.id_identite, t1.nom, t1.prenom, t1.surnom, t1.id_photo, t1.numero, t2.id_photo, t2.nom_photo, t2.nom_clair, t2.en_ligne 
FROM photo_identite AS t1
INNER JOIN photo_photo AS t2 
ON t1.id_photo = t2.id_photo 
WHERE t2.en_ligne = '0' AND t1.prenom LIKE '%bou%' OR t1.nom LIKE '%bou%' GROUP BY t1.nom, t1.prenom

Premier problème, le GROUP BY n'est pas correct ici, car il ne concerne pas toutes les colonnes qui ne font pas l'objet d'une agrégation (SUM, MIN...). Aucun SGBD autre que MySql n'excepterait cette requête.

Deuxième problème: tu mixes des AND des OR dans la clause WHERE, donc je te suggères très fortement d'utiliser des parenthèses:

Code sql :

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

1
2
3
4
5
6
 
SELECT t1.id_identite, t1.nom, t1.prenom, t1.surnom, t1.id_photo, t1.numero, t2.id_photo, t2.nom_photo, t2.nom_clair, t2.en_ligne 
FROM photo_identite AS t1
INNER JOIN photo_photo AS t2 
ON t1.id_photo = t2.id_photo 
WHERE t2.en_ligne = '0' AND (t1.prenom LIKE '%bou%' OR t1.nom LIKE '%bou%')

Tatayo.

[Artemus24]

Salut à tous.

Vous auriez dû présenter votre requête afin qu'elle soit un peu plus lisible comme çi-après :

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
SELECT   t1.id_identite,
         t1.nom,
         t1.prenom,
         t1.surnom,
         t1.id_photo,
         t1.numero,
         t2.id_photo,
         t2.nom_photo,
         t2.nom_clair,
         t2.en_ligne
 
    FROM photo_identite AS t1,
         photo_photo    AS t2
 
   WHERE t2.en_ligne = '0'
     AND t1.id_photo = t2.id_photo
     AND t1.prenom   LIKE '%bou%'
      OR t1.nom      LIKE '%bou%'
 
GROUP BY t1.nom, t1.prenom

Voici les problèmes que je constates, avant d'analyser le résultat que vous attendez :

1) vous faites un produits cartésiens entre vos deux tables.
Ce n'est pas ainsi que l'on décrit une relation entre deux tables.

2) il faut faire la distinction entre les critères appartenant à la relation entre vos deux tables, de celle qui sont dues à votre sélection.

3) il n'y a aucun descriptif de dépendance entre vos deux tables. Il faudrait envisager d'apprendre les nouvelles normes qui sont en vigueur dans l'écrire des requêtes SQL, qui selon moi, votre façon de faire date du siècle dernier.

4) vous devez mettre le même type pour la colonne "id_photo" entre vos deux tables, sinon la relation ne pourra pas se faire.

5) quand on fait un "groupe by", c'est en principe pour effectuer un regroupement.
Si le regroupement se fait sur le nom et le prénom, en admettant que ce couple soit uniquement, vous devez indiquer aux autres colonnes ce que vous désirez récupérer. C'est-à-dire n'importe quelle valeur (any_value()), la plus grande (max()), la plus petite (min()). Or ce n'est pas ce que vous faites.

Comme le dit Tatayo, ce n'est pas parce que MySql est assez souple à l'usage, qu'il faut faire n'importe quoi. D'où votre problème.

6) qu'est-ce que vient faire ici une table en MyIsam ? Pourquoi n'est-elle pas en InnoDB ?

7) quand la colonne est de type chaîne de caractères d'une longueur <255, il est préférable de mettre varchar(255).
Par contre, si la colonne n'est pas vraiment variable, comme "genre", autant mettre "char(03).

8) le test "t2.en_ligne = '0'" n'est pas correcte. Vous avez indiqué que le type est "int" alors pourquoi tester comme si c'est une chaîne de caractères ?

9) que signifie "t2.en_ligne = '0'" ? Absence de valeur ou valeur à zéro ?
Si c'est absence de valeur, dans ce cas, il faut utiliser le test "t2.en_ligne is NULL".
C'est à vous de gérer cela dans l'alimentation de cette colonne.

10) vous avez pris le premier collate ("utf8mb4_0900_ai_ci") de la liste des charset "utf8mb4", sans comprendre l'impact que cela a sur vos résultat. "utf8mb4_general_ci" aurait été préférable.

Sinon, si le langage que vous utilisez est le français, autant utiliser "latin1" avec comme collate "latin1_general_ci".

Le problème du charset "utf8mb4" fait qu'un caractère peut occuper de 1 à 4 octets, alors que "latin1" occupe seulement 1 octet.
Cela a un impact sur la volumétrie de vos tables et de surcroit sur la performance dan vos recherches.

11) Ne connaissez vous pas les index ? Cela permet d'obtenir une sélection plus rapide dans vos traitement.

12) comme l'indique Tatayo, on ne fait pas un panachage de AND et de OR comme vous le faites.
Il faut utiliser des parenthèses pour mieux respecter les priorités entre opérateurs logiques.

13) quand on fait un test de recherche d'une inclusion d'une chaîne de caractères, on fait en sorte que l'incluse commence en position 1 dans la chaîne de caractères que l'on analyse. Pourquoi ? Cela a une énorme influence sur la performance de vos résultats.

14) un point important est celle du respect de toute demande. Ou se trouve votre jeu d'essai afin de reproduire le problème que vous rencontrez chez nous ? Vous savez certainement la nature de votre problème, oui mais les explications ne m'indique pas avec précision. En l'état de votre demande, nous ne sommes pas en mesure de bien résoudre votre problème.

15) avant de venir poster pour demander à résoudre un problème, apprenez MySql. Pour cela, il existe des livres.

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
SELECT     t1.nom,
           t1.prenom,
 
           any_value(t1.id_identite) as id_identite,
           any_value(t1.surnom)      as surnom,
           any_value(t1.id_photo)    as id_photo,
           any_value(t1.numero)      as numero,
           any_value(t2.nom_photo)   as nom_photo,
           any_value(t2.nom_clair)   as nom_clair,
           any_value(t2.en_ligne)    as en_ligne
 
      FROM photo_identite AS t1
INNER JOIN photo_photo    AS t2 
        ON t2.id_photo = t1.id_photo
 
     WHERE t2.en_ligne = 0
       AND ( t1.prenom LIKE '%bou%'
        OR   t1.nom    LIKE '%bou%' )
 
  GROUP BY t1.nom, t1.prenom

Pour vos tables :

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
CREATE TABLE IF NOT EXISTS `photo_photo`
( `id_photo`  int         NOT NULL AUTO_INCREMENT primary key,
  `nom_photo` varchar(32) NOT NULL,
  `nom_clair` varchar(32) NOT NULL,
  `annee`     int         NOT NULL,
  `en_ligne`  int         NOT NULL
) ENGINE=InnoDB DEFAULT
  CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
  ROW_FORMAT=COMPRESSED;
 
CREATE TABLE IF NOT EXISTS `photo_identite`
( `id_identite` int         NOT NULL AUTO_INCREMENT primary key,
  `nom`         varchar(32) NOT NULL,
  `prenom`      varchar(32) NOT NULL,
  `surnom`      varchar(32) NOT NULL,
  `genre`          char(03) NOT NULL,
  `id_photo`    int         NOT NULL,
  `numero`      tinyint         NULL,
  INDEX 
  CONSTRAINT `FK_01` FOREIGN KEY (`id_photo`) REFERENCES `photo_photo` (`id_photo`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
  ROW_FORMAT=COMPRESSED;
 
CREATE INDEX `idx` USING BTREE ON `photo_identite` (`nom`,`prenom`,`en_ligne`);

Je ne l'ai pas testé. A vous de vous adapter à ma proposition.

Cordialement.
@+

[Equinoxe58]

Bonjour, je constate que j'ai énormément à apprendre. J'ai repris votre solution qui me donne le résultat souhaité. Je vais maintenant me pencher sur les autres requêtes de mon site, du moins pour la présentation.
Merci de m'avoir répondu

[Michel.Priori]

J'ai une requête qui ne me donne pas le résultat que je souhaite.

Désolé de le dire comme ça mais, ce que vous dites en préambule n'est pas aligné avec votre titre : "Résultat erroné dans une requête."

Sachez qu'il y a toute une communauté derrière, et que les partisans de Mysql voient d'un mauvais oeil les messages qui dénigrent leur produit favori.

Il se trouve que le contrôle d'exécution d'un GROUP BY faite par Mysql est un sujet : il est reproché à Mysql d'être trop laxiste par rapport à la norme SQL.

Donc, ne le prenez pas mal s'il vous est demandé de "bien" connaitre le SQL avant de vous servir de MySQL.
L'alternative est de changer de moteur de base de donnée ; ce qui n'est pas évident non plus.
D'une façon ou d'une autre, il va vous faloir investir du temps.

note : prenez le temps de bien apprécier les remarques d'Artemus24, il connait très bien MySQL

[Séb.]

5) quand on fait un "groupe by", c'est en principe pour effectuer un regroupement.
Si le regroupement se fait sur le nom et le prénom, en admettant que ce couple soit uniquement, vous devez indiquer aux autres colonnes ce que vous désirez récupérer. C'est-à-dire n'importe quelle valeur (any_value()), la plus grande (max()), la plus petite (min()). Or ce n'est pas ce que vous faites.

Comme le dit Tatayo, ce n'est pas parce que MySql est assez souple à l'usage, qu'il faut faire n'importe quoi. D'où votre problème.

Il se trouve que le contrôle d'exécution d'un GROUP BY faite par Mysql est un sujet : il est reproché à Mysql d'être trop laxiste par rapport à la norme SQL.

Je précise qu'en fait on a rarement vraiment besoin de ANY_VALUE() et que MySQL, avec ses réglages par défaut (ONLY_FULL_GROUP_BY actif), est compatible SQL-ANSI à ce sujet depuis MySQL 5.7.5 (soit depuis le 25/09/2014)

https://dev.mysql.com/doc/refman/8.4..._full_group_by
https://dev.mysql.com/doc/refman/8.4...-handling.html

Généralement c'est une bonne idée d'activer le mode strict. Pour tester => set @@sql_mode = 'ANSI,TRADITIONAL';