Discussion :

[Seb981]

Bonjour à tous,

J'ai 5 tables :

GARAGE : numGarage, nomGarage
OUTIL : numOutil, nomOutil
CONTROL_TECHNIQUE : numCTRLT,nomCtrlT

GARAGE_OUTIL : numGarage,numOutil,descriptionOutil
OUTIL_CTRLT : numOutil,numCtrlT

J'ai par exemple dans ma base de données :
------------------------------------------numOutil|numCtrlT
1 garage1 1 outil1 1 controle1 1 1 30mm--- 1 1
2 garage2 2 outil2 2 controle2 1 2 30mm--- 2 1
3 garage3 3 outil3 3 controle3 2 1 30mm---
4 garage4 4 outil4 4 controle4

J'ai donc des garages avec des outils et des controles techniques
avec des outils qui sont necessaires pour ce controle.

Et j'aimerais savoir quel est la requete SQL pour dire :
Je veux pour le controle technique controle1 tous les garages qui peuvent faire ce controle. (c'est à dire tous les garages qui ont comme outil : outil1 et outil 2, un garage qui aura les outils 1 et 2 mais aussi d'autre sera logiquement aussi retenu et un garage qui aura juste un des outils ne sera pas retenu)

Je fais cela :

SELECT nomgarage
FROM GARAGE,OUTIL,GARAGE_OUTIL,OUTIL_CTRLT,control_technique
WHERE GARAGE.numGarage= GARAGE_OUTIL.numgarage
AND GARAGE_OUTIL.numOutil=OUTIL.numOutil
AND OUTIL.numOutil=OUTIL_CTRLT.numOUTIL
AND OUTIL_CTRLT.numCtrlt=control_technique.numCtrlt
AND control_technique.nomCtrlt='controle1';

mais ça me donne :

garage1
garage2
garage1

or pour le controle technique controle1 il faut outil 1 et outil2
et garage 1 a outil 1 et outil 2
mais garage 2 a seulement outil1

Pourriez-vous m'aider à rendre cette requête correcte.

@+ Seb

[jfontaine]

Si je comprend bien tu veux que seul les garages qui ont les 2 outils apparraissent.

Essais cette requête

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT nomgarage, count(*) as NbOutil
FROM GARAGE,OUTIL,GARAGE_OUTIL,OUTIL_CTRLT,control_technique
WHERE GARAGE.numGarage= GARAGE_OUTIL.numgarage
AND GARAGE_OUTIL.numOutil=OUTIL.numOutil
AND OUTIL.numOutil=OUTIL_CTRLT.numOUTIL
AND OUTIL_CTRLT.numCtrlt=control_technique.numCtrlt
AND control_technique.nomCtrlt='controle1'
group by nomgarage
having count(*)=2

[fsmrel]

Si j’ai bien compris, vous recherchez les garages disposant de tous les outils nécessaires pour un contrôle donné. Si tel est le cas, il existe plusieurs solutions pour réaliser votre opération. Personnellement je m’appuie sur la division relationnelle. Malheureusement, SQL ne propose pas cet opérateur. Pour obtenir l’équivalent en s’appuyant sur la logique, il faut utiliser un double Not Exists.
Avant d’aller plus loin, je vous conseille d’étudier l’article dans lequel j’explique le pourquoi du comment (et où d’autres solutions sont proposées) :
http://www.developpez.net/forums/sho...d.php?t=350388

Dans un premier temps, repérez les tables qui sont utiles pour une requête simplifiée (on récupère le numéro du garage, sans remonter à son nom et l’on utilise la liste des outils utilisés pour le Contrôle 1 sans remonter à son nom).
Vous observez en passant que la table OUTIL n’a pas besoin de participer au jeu.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Select * 
From   GARAGE_OUTIL A
Where  Not Exists
      (Select *
       From   OUTIL_CTRLT K
       Where  Not Exists
               (Select *
                From   GARAGE_OUTIL B
                Where  A.NumGarage = B.NumGarage
                And    B.NumOutil = K.NumOutil
                And    K.NumOutil In ('outil1', 'outil2', 'outil3')
               )
      ) ;

Dans un deuxième temps, on complète la requête précédente, en faisant participer les tables GARAGE et CONTROL_TECHNIQUE.

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
 
Select Distinct G.NomGarage 
From   GARAGE G, GARAGE_OUTIL A
Where  G.NumGarage = A.NumGarage
  And  Not Exists
      (Select *
       From   OUTIL_CTRLT K
       Where  Not Exists
               (Select *
                From   GARAGE_OUTIL B, CONTROL_TECHNIQUE C 
                Where  A.NumGarage = B.NumGarage
                And    B.NumOutil = K.NumOutil
                And    C.NumCTRLT = K.NumCTRLT
                And    C.nomCtrlT = 'Controle1'  
               )
      ) ;

[SQLpro]

par des jointures à la norme (1992... soit 15 ans de retard !)

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
SELECT nomgarage
 
FROM  GARAGE as G
 
      INNER JOIN GARAGE_OUTIL as GO1
            ON G.numGarage= GO.numgarage
 
            INNER JOIN OUTIL as O1
                  ON GO1.numOutil = O1.numOutil
 
                  INNER JOIN OUTIL_CTRL as OC1
                        ON O1.numOutil = OC1.numOutil 
 
                        INNER JOIN CONTROL_TECHNIQUE as CT
                              ON OC1.numCTRL = CT.numCTRL
 
            INNER JOIN OUTIL as O2
                  ON GO2.numOutil = O2.numOutil
 
                  INNER JOIN OUTIL_CTRL as OC2
                        ON O2.numOutil = OC2.numOutil 
 
WHERE  GO1.descriptionOutil = 'outil1'
  AND  GO2.descriptionOutil = 'outil2'
  AND  CT.descriptionControme = 'controle'
  AND  OC1.numCTRL = OC2.numCTRL

A +

[fsmrel]

La requête de SQLpro ne fonctionne pas en l’état, car les noms de corrélation GO et GO2 ne sont pas définis (c’est surtout GO2 qui gêne).

Code :

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

1
2
3
4
5
6
7
SELECT nomgarage
...
ON G.numGarage= GO.numgarage
...
ON GO2.numOutil = O2.numOutil
...

Par ailleurs, si les outils nécessaires pour un contrôle sont tous définis au niveau de la table OUTIL_CTRL, alors ça n’est que si l’on restreint ceux-ci précisément à 'outil1' et 'outil2' que les restrictions sont nécessaires (GO1.descriptionOutil = 'outil1' AND GO2.descriptionOutil = 'outil2')
Personnellement, je n’ai fait figurer ces outils dans ma première requête qu’à des fins didactiques. Dans l’hypothèse où, pour un contrôle donné, ils figurent tous dans la table OUTIL_CTRL, la seconde requête que j’ai fournie suffit. S’il faut restreindre à 'outil1' et 'outil2', il suffit d’y ajouter la restriction de la première requête :
And K.NumOutil In ('outil1', 'outil2')

[Seb981]

merci à vous tous d'avoir pris le temps de m'aider.
Je vais enfin de compte utiliser la requête de fsmrel car c'est elle qui donne ce que je veux.
La premiére requête on est obliger de donner le nombre d'outil ...
et la derniére elle est très originale !

merci à vous tous et je vais lire le lien qui explique cette requête.

@+ Seb

[Seb981]

Bonjour,

Je vous écris car j'ai toujours un probleme avec ma requete, j'ai pris celle de fsmrel et lorsque j'ai pas besoin d'outil pour mon controle par exemple controle2 cela ne m'affiche aucun garage.

voici mon code :

Code :

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

1
2
3
4
5
 CREATE TABLE `control_technique` (
  `NUMCTRLT` char(32) NOT NULL default '',
  `NOMCTRLT` char(32) default NULL,
  PRIMARY KEY  (`NUMCTRLT`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Contenu de la table `control_technique`
--

Code :

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

1
2
3
4
INSERT INTO `control_technique` VALUES ('1', 'controle1');
INSERT INTO `control_technique` VALUES ('2', 'controle2');
INSERT INTO `control_technique` VALUES ('3', 'controle3');
INSERT INTO `control_technique` VALUES ('4', 'controle4');

-- --------------------------------------------------------
--
-- Structure de la table `garage`
--

Code :

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

1
2
3
4
5
CREATE TABLE `garage` (
  `NUMGARAGE` int(11) NOT NULL default '0',
  `NOMGARAGE` char(32) default NULL,
  PRIMARY KEY  (`NUMGARAGE`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Contenu de la table `garage`
--

Code :

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

1
2
3
4
INSERT INTO `garage` VALUES (1, 'garage1');
INSERT INTO `garage` VALUES (2, 'garage2');
INSERT INTO `garage` VALUES (3, 'garage3');
INSERT INTO `garage` VALUES (4, 'garage4');

-- --------------------------------------------------------
--
-- Structure de la table `garage_outil`
--

Code :

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

1
2
3
4
5
6
7
CREATE TABLE `garage_outil` (
  `NUMGARAGE` int(11) NOT NULL default '0',
  `NUMOUTIL` int(11) NOT NULL default '0',
  `DESCRIPTIONOUTIL` char(32) default NULL,
  PRIMARY KEY  (`NUMGARAGE`,`NUMOUTIL`),
  KEY `FK_GARAGE_OUTIL_OUTIL` (`NUMOUTIL`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Contenu de la table `garage_outil`
--

Code :

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

1
2
3
4
5
6
7
INSERT INTO `garage_outil` VALUES (1, 1, '30mm');
INSERT INTO `garage_outil` VALUES (1, 2, '40mm');
INSERT INTO `garage_outil` VALUES (2, 1, NULL);
INSERT INTO `garage_outil` VALUES (2, 3, NULL);
INSERT INTO `garage_outil` VALUES (3, 1, NULL);
INSERT INTO `garage_outil` VALUES (3, 2, NULL);
INSERT INTO `garage_outil` VALUES (3, 3, NULL);

-- --------------------------------------------------------
--
-- Structure de la table `outil`
--

Code :

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

1
2
3
4
5
CREATE TABLE `outil` (
  `NUMOUTIL` int(11) NOT NULL default '0',
  `NOMOUTIL` char(32) default NULL,
  PRIMARY KEY  (`NUMOUTIL`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Contenu de la table `outil`
--

Code :

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

1
2
3
4
INSERT INTO `outil` VALUES (1, 'outil1');
INSERT INTO `outil` VALUES (2, 'outil2');
INSERT INTO `outil` VALUES (3, 'outil3');
INSERT INTO `outil` VALUES (4, 'outil4');

-- --------------------------------------------------------
--
-- Structure de la table `outil_ctrlt`
--

Code :

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

1
2
3
4
5
6
CREATE TABLE `outil_ctrlt` (
  `NUMCTRLT` char(32) NOT NULL default '',
  `NUMOUTIL` int(11) NOT NULL default '0',
  PRIMARY KEY  (`NUMCTRLT`,`NUMOUTIL`),
  KEY `FK_OUTIL_CTRLT_OUTIL` (`NUMOUTIL`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Contenu de la table `outil_ctrlt`
--

Code :

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

1
2
INSERT INTO `outil_ctrlt` VALUES ('1', 1);
INSERT INTO `outil_ctrlt` VALUES ('1', 2);

voici ma requête :

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
SELECT DISTINCT G.NomGarage 
FROM   GARAGE G, GARAGE_OUTIL A
WHERE  G.NumGarage = A.NumGarage
  AND  NOT EXISTS
      (SELECT *
       FROM   OUTIL_CTRLT K
       WHERE  NOT EXISTS
               (SELECT *
                FROM   GARAGE_OUTIL B, CONTROL_TECHNIQUE C 
                WHERE  A.NumGarage = B.NumGarage
                AND    B.NumOutil = K.NumOutil
                AND    C.NumCTRLT = K.NumCTRLT
                AND    C.nomCtrlT = 'controle2'  
               )
      )

j'ai passé toute mon après-midi mais là j'ai vraiment pas d'idée !

@+ Seb

[fsmrel]

On est distrait ? Personnellement, je remplacerais volontiers

AND C.nomCtrlT = 'controle2'

par

AND C.nomCtrlT = 'controle1'

En effet, selon vos inserts, seul le contrôle contrôle1 est associé une liste d'outils (table OUTIL_CTRLT)...

[Seb981]

merci pour votre message.

Mais pour le controle : controle2 comme il n'a pas besoin d'outil particulier on devrait pouvoir faire le controle dans n'importe quel garage. Or avec cette requête on a aucune valeur.

Est-ce que on peut faire cela avec une requête SQL ou faut-il modifier ma base de données ?

[fsmrel]

Bonsoir Seb981,


Si tous les garages sont concernés quand un contrôle ne nécessite pas d’outil, alors il faut compléter la requête. Appelons R1 cette requête initiale. Il procéder à l’union de R1 et d’une requête R2, telle que cette dernière retourne l’ensemble vide si le contrôle impliqué nécessite des outils, l’ensemble des garages sinon.

Construction de R2 :
Dans un 1er temps, on peut par exemple utiliser COUNT(*) pour jouer le rôle la valeur de vérité Vrai | Faux (COUNT(*) = 1 si un contrôle nécessite des outils, 0 sinon) :

Code :

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

1
2
3
4
5
6
7
8
SELECT   COUNT (*)
FROM     CONTROL_TECHNIQUE C
WHERE    EXISTS
        (SELECT   *
         FROM     OUTIL_CTRLT K
         WHERE    C.NumCTRLT = K.NumCTRLT
         AND      C.nomCtrlT = 'controlex' ;

Dans un deuxième temps, on récupère les garages (c'est-à-dire tous les garages), quand les contrôles ne nécessitent pas d’outils et uniquement dans ce cas-là (COUNT(*) doit donc être égal à 0) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT   NomGarage
FROM     GARAGE 
WHERE   (SELECT   COUNT (*)
         FROM     CONTROL_TECHNIQUE C
         WHERE    EXISTS
                 (SELECT   *
                  FROM     OUTIL_CTRLT K
                  WHERE    C.NumCTRLT = K.NumCTRLT
                  AND      C.nomCtrlT = 'controlex'
                 )
         ) = 0 ;

Et finalement on procède à l’union des requêtes R1 et R2 :

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
SELECT   NomGarage
FROM     GARAGE 
WHERE   (SELECT   COUNT (*)
         FROM     CONTROL_TECHNIQUE C
         WHERE    EXISTS
                 (SELECT   *
                  FROM     OUTIL_CTRLT K
                  WHERE    C.NumCTRLT = K.NumCTRLT
                  AND      C.nomCtrlT = 'controlex'
                 )
         ) = 0
UNION
SELECT  G.NomGarage  -- cas du controle necessitant des outils
FROM    GARAGE G, GARAGE_OUTIL A
WHERE   G.NumGarage = A.NumGarage
  AND   NOT EXISTS
       (SELECT *
        FROM   OUTIL_CTRLT K
        WHERE  NOT EXISTS
              (SELECT  *
               FROM    GARAGE_OUTIL B, CONTROL_TECHNIQUE C 
               WHERE   A.NumGarage = B.NumGarage
               AND     B.NumOutil = K.NumOutil
               AND     C.NumCTRLT = K.NumCTRLT
               AND     C.nomCtrlT = 'controlex'
              )
      ) ;

J'espère ne rien avoir oublié...

[Seb981]

merci beaucoup de continuer à m'aider.

Si je fais ce cas là avec cette base de données et cette requête je ne comprend pas pourquoi cela ne marche pas pour le controle2.
( le controle2 il lui faut l'outil 1 : le garage1,2 et 3 là et cela ne m'affiche aucun garage )

REQUETE

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
SELECT  G.NomGarage
FROM    GARAGE G, GARAGE_OUTIL A
WHERE   G.NumGarage = A.NumGarage
  AND   NOT EXISTS
       (SELECT *
        FROM   OUTIL_CTRLT K
        WHERE  NOT EXISTS
              (SELECT  *
               FROM    GARAGE_OUTIL B, CONTROL_TECHNIQUE C 
               WHERE   A.NumGarage = B.NumGarage
               AND     B.NumOutil = K.NumOutil
               AND     C.NumCTRLT = K.NumCTRLT
               AND     C.nomCtrlT = 'controle2'
              )
      )

BASE DE DONNEES

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
-- phpMyAdmin SQL Dump
-- version 2.6.1
-- http://www.phpmyadmin.net
-- 
-- Serveur: localhost
-- Généré le : Vendredi 22 Juin 2007 à 17:07
-- Version du serveur: 4.1.9
-- Version de PHP: 4.3.10
-- 
-- Base de données: `garage`
-- 
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `control_technique`
-- 
 
CREATE TABLE `control_technique` (
  `NUMCTRLT` char(32) NOT NULL default '',
  `NOMCTRLT` char(32) default NULL,
  PRIMARY KEY  (`NUMCTRLT`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- 
-- Contenu de la table `control_technique`
-- 
 
INSERT INTO `control_technique` VALUES ('1', 'controle1');
INSERT INTO `control_technique` VALUES ('2', 'controle2');
INSERT INTO `control_technique` VALUES ('3', 'controle3');
INSERT INTO `control_technique` VALUES ('4', 'controle4');
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `garage`
-- 
 
CREATE TABLE `garage` (
  `NUMGARAGE` int(11) NOT NULL default '0',
  `NOMGARAGE` char(32) default NULL,
  PRIMARY KEY  (`NUMGARAGE`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- 
-- Contenu de la table `garage`
-- 
 
INSERT INTO `garage` VALUES (1, 'garage1');
INSERT INTO `garage` VALUES (2, 'garage2');
INSERT INTO `garage` VALUES (3, 'garage3');
INSERT INTO `garage` VALUES (4, 'garage4');
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `garage_outil`
-- 
 
CREATE TABLE `garage_outil` (
  `NUMGARAGE` int(11) NOT NULL default '0',
  `NUMOUTIL` int(11) NOT NULL default '0',
  `DESCRIPTIONOUTIL` char(32) default NULL,
  PRIMARY KEY  (`NUMGARAGE`,`NUMOUTIL`),
  KEY `FK_GARAGE_OUTIL_OUTIL` (`NUMOUTIL`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- 
-- Contenu de la table `garage_outil`
-- 
 
INSERT INTO `garage_outil` VALUES (1, 1, '30mm');
INSERT INTO `garage_outil` VALUES (1, 2, '40mm');
INSERT INTO `garage_outil` VALUES (2, 1, NULL);
INSERT INTO `garage_outil` VALUES (2, 3, NULL);
INSERT INTO `garage_outil` VALUES (3, 1, NULL);
INSERT INTO `garage_outil` VALUES (3, 2, NULL);
INSERT INTO `garage_outil` VALUES (3, 3, NULL);
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `outil`
-- 
 
CREATE TABLE `outil` (
  `NUMOUTIL` int(11) NOT NULL default '0',
  `NOMOUTIL` char(32) default NULL,
  PRIMARY KEY  (`NUMOUTIL`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- 
-- Contenu de la table `outil`
-- 
 
INSERT INTO `outil` VALUES (1, 'outil1');
INSERT INTO `outil` VALUES (2, 'outil2');
INSERT INTO `outil` VALUES (3, 'outil3');
INSERT INTO `outil` VALUES (4, 'outil4');
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `outil_ctrlt`
-- 
 
CREATE TABLE `outil_ctrlt` (
  `NUMCTRLT` char(32) NOT NULL default '',
  `NUMOUTIL` int(11) NOT NULL default '0',
  PRIMARY KEY  (`NUMCTRLT`,`NUMOUTIL`),
  KEY `FK_OUTIL_CTRLT_OUTIL` (`NUMOUTIL`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- 
-- Contenu de la table `outil_ctrlt`
-- 
 
INSERT INTO `outil_ctrlt` VALUES ('1', 1);
INSERT INTO `outil_ctrlt` VALUES ('1', 2);
INSERT INTO `outil_ctrlt` VALUES ('2', 1);

et je ne comprend pas pourquoi en rajoutant dans ma base de données
"controle 2 lui faut l'outil 1 " rend fausse la requête 1 et on retrouve le même résultat qu'avant dans la requête 2 lorsque l'on précise pas le nom du controle.

REQUETE 1

Code :

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

SELECT G.NomGarage FROM GARAGE G, GARAGE_OUTIL A WHERE G.NumGarage = A.NumGarage AND NOT EXISTS  (SELECT * FROM OUTIL_CTRLT K WHERE NOT EXISTS  (SELECT * FROM GARAGE_OUTIL B, CONTROL_TECHNIQUE C WHERE A.NumGarage = B.NumGarage AND B.NumOutil = K.NumOutil AND C.NumCTRLT = K.NumCTRLT AND C.nomCtrlT = 'controle1'  )  )

REQUETE 2
( comme requête 1 sans AND C.nomCtrlT = 'controle1' )

Code :

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

SELECT G.NomGarage FROM GARAGE G, GARAGE_OUTIL A WHERE G.NumGarage = A.NumGarage AND NOT EXISTS  (SELECT * FROM OUTIL_CTRLT K WHERE NOT EXISTS  (SELECT * FROM GARAGE_OUTIL B, CONTROL_TECHNIQUE C WHERE A.NumGarage = B.NumGarage AND B.NumOutil = K.NumOutil AND C.NumCTRLT = K.NumCTRLT   )  )

[fsmrel]

Bonjour Seb,

Dans le cas de 'controle2', la requête ne fonctionne pas. Je plaide coupable : la requête est à remplacer par la suivante, identique, à ceci près que le test de la valeur 'controlex' est déplacé :

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
SELECT  G.NomGarage 
FROM    GARAGE G, GARAGE_OUTIL A
WHERE   G.NumGarage = A.NumGarage
  AND   NOT EXISTS
       (SELECT *
        FROM   OUTIL_CTRLT K
        WHERE  NOT EXISTS
              (SELECT  *
               FROM    GARAGE_OUTIL B, CONTROL_TECHNIQUE C 
               WHERE   A.NumGarage = B.NumGarage
               AND     B.NumOutil = K.NumOutil
               AND     C.NumCTRLT = K.NumCTRLT
              )
          AND  K.NumCTRLT = (SELECT  NumCTRLT 
                             FROM    CONTROL_TECHNIQUE
                             WHERE   nomCTRLT = 'controlex')
      ) ;

En effet, NOT EXISTS ( ... AND C.nomCtrlT = 'controlex') revient à ne pas retenir 'controlex' alors qu’il le faut...
Cela fonctionne pour la première requête de l’Union parce que le bloc ne comporte pas de négation :
EXISTS ( ... AND C.nomCtrlT = 'controlex').
Pour des raisons d’homogénéité, vous pouvez externaliser là aussi le test de 'controlex', mais ça n’est pas une obligation...


N’hésitez pas si vous constatez d’autres anomalies...

NB. Quand vous codez : "SELECT DISTINCT G.NomGarage", vous pouvez supprimer la clause DISTINCT : le tri provoqué par l’UNION éliminant les doublons.

[Seb981]

merci beaucoup d'avoir encore pris le temps de m'aider.

Je suis toujours en phase d'analyse pour ce projet et je m'inquiete à l'avance de savoir si je peux faire cette requête avec la base de données que j'ai crée ou si je dois la remettre en question.

Je vais cette semaine refaire des tests avec cette requête.

Merci,

Seb

[fsmrel]

Bonjour Seb,


Si ce qui suit vous donne la migraine, vous pouvez vous concentrer sur les requêtes, mais j’espère que ce que j’ai écrit est quand même à peu près digeste...

Je me suis replongé dans les ouvrages de Chris Date et Hugh Darwen, afin de repartir des fondamentaux et fournir une solution collant de très près à la théorie. En fait, je propose deux solutions équivalentes, la première utilisant la logique des prédicats et la seconde utilisant l’algèbre relationnelle.
Pour cela, il faut partir de la division relationnelle revue par D & D au début des années quatre-vingt-dix, et qui s’écrit désormais ainsi :

A DIVIDEBY B PER AB

L’opérateur DIVIDEBY PER permet de prendre en compte ce qui vous concerne plus particulièrement, à savoir la division par zéro, illustrée par les contrôles techniques requérant un nombre d’outils égal à 0. Avant que D & D ne travaillent la question, cette situation avait jusque-là été escamotée dans les ouvrages traitant de la division (Soit R et S deux relations de degrés respectifs r et s, telles que r > s et S soit non vide...)

Dans ce qui suit, les termes formels suivants : relation, tuple, entête, peuvent être interprétés ainsi : table, ligne, schéma de table.


Solution 1 - Application de la théorie de la quantification

Soit a, b, c trois relations ayant respectivement pour entête : {X}, {X, Y}, {Y}.

Le résultat de l’opération a DIVIDEBY b PER c est une relation ayant pour entête {X} et dont le corps est constitué de tous les tuples {X x} présents dans a, tels que, pour tous les tuples {Y y} présents dans b, le tuple {X x, Y y} est présent dans c.

Dans le cas qui nous intéresse, le résultat de l’opération Garage DIVIDEBY Outil_Ctrlt PER Garage_Outil est une relation ayant pour entête {numGarage} et dont le corps est constitué de tous les tuples {numGarage x} présents dans Garage, tels que, pour tous les tuples {numOutil y} présents dans Outil_Ctrlt, le tuple {numGarage x, numOutil y} est présent dans Garage_Outil.

Dans le style de la logique :

EXISTS g FORALL co EXISTS go (g.numGarage = go.numGarage AND go.numOutil = co.numOutil)

Ceci ne peut pas être traduit directement en SQL. En effet, si ce langage propose le prédicat EXISTS (contrepartie du quantificateur existentiel de la logique), en revanche il ne propose pas de prédicat FORALL (contrepartie du quantificateur universel de la logique). On a quand même une solution de secours, puisque l’on sait on sait exprimer la quantification universelle au moyen de la quantification existentielle :

D’où la reformulation :

EXISTS g NOT EXISTS co NOT EXISTS go (g.numGarage = go.numGarage AND go.numOutil = co.numOutil)

Et la traduction en SQL :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Select  numGarage
From    Garage g
Where   Not Exists
       (Select  *
        From    Outil_Ctrlt co
        Where   Not Exists
               (Select  *
                From    Garage_Outil go
                Where   g.numGarage = go.numGarage   
                  And   go.numOutil = co.numOutil
               )
       ) ;

Je n’ai pas encore intégré le fait que le résultat vaut pour un contrôle donné. Soit numCtrlt = 'x' le numéro de ce contrôle, en conséquence la requête devient :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Select  numGarage
From    Garage g
Where   Not Exists
       (Select  *
        From    Outil_Ctrlt co
        Where   Not Exists
               (Select  *
                From    Garage_Outil go
                Where   g.numGarage = go.numGarage   
                  And   go.numOutil = co.numOutil
               )
          And   numctrlt = 'x'
       ) ;

Et si l’on veut faire mention du nom du contrôle plutôt que de son numéro :

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
Select  numGarage
From    Garage g
Where   Not Exists
       (Select  *
        From    Outil_Ctrlt co
        Where   Not Exists
               (Select  *
                From    Garage_Outil go
                Where   g.numGarage = go.numGarage   
                  And   go.numOutil = co.numOutil
               )
          And   numctrlt = (Select numCtrlt 
                            From   Control_Technique
                            Where  nomCtrlt = 'controlex')
       ) ;

La rapidité d’exécution de cette requête devrait être satisfaisante, sous réserve que les attributs impliqués soient correctement indexés. Au besoin, la requête précédente pourrait être un poil plus rapide, du fait que l’on économise un jointure. Cela dit, la table Control_Technique ne devrait pas faire des millions de lignes...


Solution 2 - Utilisation des seuls opérateurs relationnels

L’opérateur DIVIDEBY PER peut être exprimé à l’aide des opérateurs MINUS (Différence, appelé EXCEPT au niveau de la norme SQL) et TIMES (produit cartésien). En algèbre relationnelle :

A DIVIDEBY B PER AB

a pour équivalent :

A MINUS ((A TIMES B) MINUS AB {X, Y}) {X}

AB {X, Y} désigne la projection de AB sur les colonnes X et Y.

En remplaçant A, B et AB respectivement par Garage, Outil_Ctrlt et Garage_Outil,
en remplaçant X par NumGarage, et Y par NumOutil, le résultat voulu (au numéro de contrôle près) est obtenu ainsi :

Garage MINUS ((Garage TIMES Outil_Ctrlt) MINUS Garage_Outil {NumGarage, NumOutil}) {NumGarage}

Où il suffit d’ajouter la restriction sur le contrôle requis, que j’abrège ici en Outil_Ctrlt Where numCtrlt = 'x' :

Garage MINUS ((Garage TIMES Outil_Ctrlt Where numCtrlt = 'x') MINUS Garage_Outil {NumGarage, NumOutil}) {NumGarage}

Sinon, pour être complet :

Garage MINUS ((Garage TIMES Outil_Ctrlt Where numCtrlt = (Control_Technique Where nomCtrlt = 'controlex'){numCtrlt}) MINUS Garage_Outil {NumGarage, NumOutil}) {NumGarage}

Et la traduction SQL

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
Select  numGarage
From    Garage
Except
Select  numGarage 
From   (Select numGarage, numOutil
        From   Garage, 
               Outil_Ctrlt  
        Where  numCtrlt = (Select numCtrlt 
                           From   Control_Technique
                           Where  nomCtrlt = 'controlex')    
        Except
        Select numGarage, numOutil
        From   garage_outil  
       ) as a
;

Maintenant, je ne m’engagerai pas quant à la rapidité d’exécution, il peut y avoir du tuning à prévoir...

En tout cas, pas question de toucher aux tables à cause d’une p... de requête ! Si la voie de la face Nord est bouchée, il y en a d'autres...

En attendant que nous disposions de l’opérateur de division, je vous souhaite bon courage,

Fsmrel

[Seb981]

Merci pour tout.

Cette requête me servira plus tard pour la fonction recherche de mon site.

Pourrais-tu me dire si on peut rajouter d'autre critére dans cette requête sans la rendre fausse ( il faut aussi que je recherche sur le lieu ... )
sachant que je viens d'essayer, cela marche si on rajoute sur la requête1* : si un controle n'a pas d'outils et sur la requête 2 si un controle a des outils sinon elles ignorent. Sachant qu'il faut aussi que je prenne en compte que je peux rechercher sur tous les controles sans forcement en choisir un ...

J'espere que tu pourras me conseiller,

@+ Seb

*( Requête1 UNION Requête2 )

[fsmrel]

Pourrais-tu me dire si on peut rajouter d'autre critére dans cette requête sans la rendre fausse.

Certes, mais les tests devront être particulièrement soignés.

sinon elles ignorent.

On dirait une réplique des Tontons flingueurs...
Elles ignorent quoi ?

Merci de formuler de façon rigoureuse, car le contexte n'est pas simple...

[Seb981]

Bonjour,

On veut par exemple tous les garages : qui peuvent faire le contrôle 1 et qui ont l’outil 3.

Cas avec un contrôle qui a besoin d’outils.

Si je mets numOutil dans la requête 2 c’est pris en compte (requête A)
Si je mets numOutil dans la requête 1 cette demande n’est pas pris en compte, seule le contrôle est pris en compte (requête B) (d’où le « elles ignorent » … le numOutil)

Mais malheureusement on ne sait pas à l’avance si le contrôle possède des outils et donc si il faut mettre la demande numoutil dans la requête 1 ou dans la requête 2.

Requête A avec R1 UNION R2

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
SELECT   NomGarage
FROM     GARAGE 
WHERE   (SELECT   COUNT(*)
         FROM     CONTROL_TECHNIQUE C
         WHERE    EXISTS
                 (SELECT*
                  FROM     OUTIL_CTRLT K
                  WHERE    C.NumCTRLT = K.NumCTRLT
                  AND      C.numCtrlT = 1
                 )
         ) = 0
UNION
SELECT  G.NomGarage 
FROM    GARAGE G, GARAGE_OUTIL A
WHERE G.NumGarage = A.NumGarage
AND numOutil = 3
  AND   NOT EXISTS
       (SELECT *
        FROM   OUTIL_CTRLT K
        WHERE  NOT EXISTS
              (SELECT  *
               FROM    GARAGE_OUTIL B, CONTROL_TECHNIQUE C 
               WHERE   A.NumGarage = B.NumGarage
               AND     B.NumOutil = K.NumOutil
               AND     C.NumCTRLT = K.NumCTRLT
              )
          AND  K.NumCTRLT = 1
      )

Requete B avec R1 UNION R2

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
SELECT   NomGarage
FROM     GARAGE, GARAGE_OUTIL
WHERE   garage.numgarage = garage_outil.numgarage
AND numOutil = 3
AND (SELECT   COUNT(*)
         FROM     CONTROL_TECHNIQUE C
         WHERE    EXISTS
                 (SELECT*
                  FROM     OUTIL_CTRLT K
                  WHERE    C.NumCTRLT = K.NumCTRLT
                  AND      C.numCtrlT = 1
                 )
         ) = 0
UNION
SELECT  G.NomGarage 
FROM    GARAGE G, GARAGE_OUTIL A
WHERE G.NumGarage = A.NumGarage
  AND   NOT EXISTS
       (SELECT *
        FROM   OUTIL_CTRLT K
        WHERE  NOT EXISTS
              (SELECT  *
               FROM    GARAGE_OUTIL B, CONTROL_TECHNIQUE C 
               WHERE   A.NumGarage = B.NumGarage
               AND     B.NumOutil = K.NumOutil
               AND     C.NumCTRLT = K.NumCTRLT
              )
          AND  K.NumCTRLT = 1
      )

Cas avec un contrôle sans outils

On veut par exemple tous les garages : qui peuvent faire le Contrôle 3 et qui ont l’outil 3

Il faudrait ignorer la requête 2 pour trouver le bon résultat, c'est-à-dire garage 2 et garage 3.

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
SELECT   NomGarage
FROM     GARAGE, GARAGE_OUTIL
WHERE   garage.numgarage = garage_outil.numgarage
AND garage_outil.numOutil = 3
AND (SELECT   COUNT(*)
         FROM     CONTROL_TECHNIQUE C
         WHERE    EXISTS
                 (SELECT*
                  FROM     OUTIL_CTRLT K
                  WHERE    C.NumCTRLT = K.NumCTRLT
                  AND      C.numCtrlT = 3
                 )
         ) = 0
UNION
SELECT  G.NomGarage 
FROM    GARAGE G, GARAGE_OUTIL A
WHERE   G.NumGarage = A.NumGarage
AND NOT EXISTS
       (SELECT *
        FROM   OUTIL_CTRLT K
        WHERE  NOT EXISTS
              (SELECT  *
               FROM    GARAGE_OUTIL B, CONTROL_TECHNIQUE C 
               WHERE   A.NumGarage = B.NumGarage
               AND     B.NumOutil = K.NumOutil
               AND     C.NumCTRLT = K.NumCTRLT
              )
          AND  K.NumCTRLT = 3
      )

Pourrais-tu me dire si c’est possible de gérer ces cas.

Seb

[Seb981]

Ne pas prendre en compte mon précédent message.

Grâce à votre requête simplifié cela me pose plus aucun problème : cette requête gére les cas ou :

- cas ou 1 garage posséde 1 outil sur 2 pour un contrôle donné
- cas ou un contrôle n'a pas besoin d'outil
- cas ou aucun garage a l'outil nécessaire au contrôle donné.

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
Select  nomGarage
From    Garage g, Garage_outil go
Where g.numgarage=go.numgarage
AND numoutil = 3
AND Not Exists
       (Select  *
        From    Outil_Ctrlt co
        Where   Not Exists
               (Select  *
                From    garage_outil go
                Where   g.numGarage = go.numGarage   
                  And   go.numOutil = co.numOutil
               )
          And   co.numctrlt = (Select numCtrlt 
                               From   Control_Technique
                               Where  numCtrlt = 1 )            
       )

merci pour tout,

@+ Seb

[fsmrel]

Bonsoir Seb,


Si tout baigne, tant mieux !

Juste une petite remarque. Quand vous codez :

Code :

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

1
2
3
And   co.numctrlt = (Select numCtrlt 
                               From   Control_Technique
                               Where  numCtrlt = 1)

Vous pouvez simplifier :

Code :

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

And   co.numctrlt = 1

Et ne faire mention de Control_Techique que si cela est indispensable :

Code :

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

1
2
3
And   co.numctrlt = (Select numCtrlt 
                               From   Control_Technique
                               Where  nomCtrlt = 'controle1')

La parcimonie est une règle précieuse concernant les bases de données...

Bonne route

[Seb981]

merci pour cette remarque.

Seb