Requête avec deux tables m:m

**Seb981** · 31/08/2007, 15h08

Bonjour à tous,

Je vous écris car j'ai un problème pour faire une requête avec deux tables m:m.

Mes tables sont :

ControleTechnique_Outil : numCTL, numOUTIL
Outil : numOUTIL
Garage_Outil : numGARAGE, numOUTIL

Et il faudrait que j'ai tous les controles technique possible à faire sur un garage.

Un controle technique est possible à faire sur un garage si le controle possede les outils que le garage possede. MAIS le CTL n'est pas obliger d'avoir besoin de tous les outils du garage et peut ne pas avoir besoin d'outil pour être réalisable sur un garage.

J'ai fait la requête :

Code :

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

1
2
3
4
SELECT numCTL
FROM ControleTechnique_Outil cto ,Garage_Outil go
WHERE cto.numOUTIL = go.numOUTIL
AND numgarage = 1

mais malheureusement cela ne marche pas.

Pourriez-vous m'aider à y voir plus clair ?

Si ce n'est pas clair n'hesitez surtout pas à me poser des questions.

**metfan** · 31/08/2007, 15h18

Essaye:

SELECT cto.numCTL
FROM ControleTechnique_Outil cto ,Garage_Outil go
WHERE cto.numOUTIL = go.numOUTIL
AND go.numgarage = 1

**Pergos** · 31/08/2007, 15h23

Bon, très barbare et donc à optimiser, mais si je comprend ton problème, une piste pourrait être :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT DISTINCT cto.numCTL
FROM ControleTechnique_Outil cto 
WHERE NOT EXISTS (SELECT numOUTIL 
                  FROM ControleTechnique_Outil cto2 
                  WHERE cto2.numCTL=cto.numCTL
                    AND NOT cto2.numOUTIL IN (SELECT numOUTIL 
                                              FROM Garage_Outil 
                                              WHERE go.numgarage = 1) )

**AL1986** · 31/08/2007, 15h38

Envoyé par Seb981

mais malheureusement cela ne marche pas.

Qu'est ce qui ne marche pas ?
Y a-t-il des erreurs ?
Sinon, Envoie un petit exemple en exprimant le résultat souhaité et le résultat obtenu

Envoyé par metfan

Essaye:

SELECT cto.numCTL
FROM ControleTechnique_Outil cto ,Garage_Outil go
WHERE cto.numOUTIL = go.numOUTIL
AND go.numgarage = 1

Je doute que cela soit un pb d'alias, il ne peut pas vraiment y avoir de confusions puisque les deux seules tables présentes dans la requête sont cto et go et que le champ numCTL est propre à ControleTechnique_Outil et numgarage est propre à Garage_Outil. Mais on sait jamais.

**Seb981** · 31/08/2007, 15h50

Merci à vous trois pour votre aide. Toutes les requêtes marchent mais ne donne pas le résultat qu'il me faut.

Je vais vous montrer un exemple :

On a :

ControleTechnique_Outil
numOUTIL numCTL
1 1
4 1
40 1

Garage_Outil
numOUTIL numGARAGE
1 3
4 3
40 3
47 3
28 3
19 3
....

1 4
4 4
40 4
41 4
22 4
13 4
...

1 5
40 5
41 5
22 5
13 5
...

Le garage 3 pourra faire le CTL 1
Le garage 4 pourra faire le CTL 1
Le garage 5 pas le CTL1 car il manque l'outil 4

Donc si je veux savoir
- quel controle est possible pour le garage 3 ? on me dira : ctl 1
- quel controle est possible pour le garage 4 ? on me dira : ctl 1
- quel controle est possible pour le garage 5 ? on me dira : aucun

**Seb981** · 31/08/2007, 16h10

Mais je pense qu'il faut utiliser aussi la table CTL (controle technique) car si on a un controle qui demande pas d'outil on le retrouvera pas dans la table ControleTechnique_Outil.

**Seb981** · 01/09/2007, 14h17

Mes explications étant confus je vous ai fais un exemple simplifié de ma base de données.

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
-- Base de données: `garagiste`
-- 
 
-- --------------------------------------------------------
 
-- 
 
-- Structure de la table `controle`
-- 
 
CREATE TABLE `controle` (
  `NUMCTL` char(32) NOT NULL default '',
  `NOMCTL` char(32) default NULL,
  PRIMARY KEY  (`NUMCTL`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- 
-- Contenu de la table `controle`
-- 
 
INSERT INTO `controle` VALUES ('1', 'controle1');
INSERT INTO `controle` VALUES ('2', 'controle2');
INSERT INTO `controle` VALUES ('3', 'controle3');
INSERT INTO `controle` VALUES ('4', 'controle4');
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `ctl_outil`
-- 
 
CREATE TABLE `ctl_outil` (
  `NUMCTL` char(32) NOT NULL default '',
  `NUMOUTIL` char(32) NOT NULL default '',
  PRIMARY KEY  (`NUMCTL`,`NUMOUTIL`),
  KEY `FK_CTL_OUTIL_OUTIL` (`NUMOUTIL`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- 
-- Contenu de la table `ctl_outil`
-- 
 
INSERT INTO `ctl_outil` VALUES ('1', '1');
INSERT INTO `ctl_outil` VALUES ('1', '2');
INSERT INTO `ctl_outil` VALUES ('2', '1');
INSERT INTO `ctl_outil` VALUES ('3', '2');
INSERT INTO `ctl_outil` VALUES ('3', '4');
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `garage`
-- 
 
CREATE TABLE `garage` (
  `NUMGARAGE` char(32) NOT NULL default '',
  `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` (
  `NUMOUTIL` char(32) NOT NULL default '',
  `NUMGARAGE` char(32) NOT NULL default '',
  PRIMARY KEY  (`NUMOUTIL`,`NUMGARAGE`),
  KEY `FK_GARAGE_OUTIL_GARAGE` (`NUMGARAGE`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
-- 
-- Contenu de la table `garage_outil`
-- 
 
INSERT INTO `garage_outil` VALUES ('1', '1');
INSERT INTO `garage_outil` VALUES ('2', '1');
INSERT INTO `garage_outil` VALUES ('3', '1');
INSERT INTO `garage_outil` VALUES ('3', '2');
INSERT INTO `garage_outil` VALUES ('4', '1');
INSERT INTO `garage_outil` VALUES ('4', '3');
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `outil`
-- 
 
CREATE TABLE `outil` (
  `NUMOUTIL` char(32) NOT NULL default '',
  `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');

Je voudrais donc savoir pour un garage donnée ex(garage1) les controles qui sont disponibles dessus.

Le garage 1 qui a les outils 1,2,3,4 peut faire les controles :
1 car le ctl a besoin de l'outil 1 et 2
2 car le ctl a besoin de l'outil 1
3 car le ctl a besoin de l'outil 2 et 4
4 car le ctl n'a pas besoin d'outil particulier

Le garage 2 qui a l' outil 3 peut faire les controles :
4 car le ctl n'a pas besoin d'outil particulier

Le garage 3 qui a l' outil 4 peut faire les controles :
4 car le ctl n'a pas besoin d'outil particulier

Le garage 4 qui n'a pas d'outil peut faire les controles :
4 car le ctl n'a pas besoin d'outil particulier

Voilà, j'espere que j'ai été plus clair.
Je pense qu'il faut utiliser des sous requetes avec exists ou not exists mais je n'arrive pas à avoir les résultats ci-dessus.
J'espere que vous pourrez m'aider.

**Seb981** · 03/09/2007, 13h42

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT nomCtl
FROM Controle ctl
WHERE EXISTS (
 
SELECT *
FROM GARAGE_OUTIL go, CTL_OUTIL ctlo
WHERE ctl.numCtl = ctlo.numCtl
AND ctlo.numoutil = go.numoutil
AND go.numGarage =3
)

J'ai fais cette requête sql en m'aidant des requêtes ci-dessus mais je n'arrive toujours pas au résultat attendu.

Avez-vous une idée ?

**al1_24** · 03/09/2007, 13h58

Tu veux savoir si le garage a tous les outils pour un contrôle.
Donc s'il ne lui manque pas un outil pour ce contrôle.

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
SELECT  go.numgarage
    ,   co.numctl
FROM    garage_outil    AS go
    INNER JOIN
        ctl_outil   AS co
        ON  go.numoutil = co.numoutil
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    ctl_outil   AS co2
                LEFT JOIN
                    garage_outil AS go2
                    ON  go2.numoutil    = co2.numoutil
                    AND go2.numgarage   = go.numgarage
            WHERE   co2.numctl  = co.numctl
                AND go2.numoutil IS NULL
        )
GROUP BY go.numgarage
    ,   co.numctl
;

**Seb981** · 03/09/2007, 14h07

Merci pour ton aide al1_24.

J'aimerais savoir pour un garage donnée ex(garage1) les controles qui sont disponibles dessus.

Comme donnée au début j'ai un numéro de garage et en sortie j'aimerai avoir les numéro des controles possibles.

Je viens d'essayer ta requêtes mais je n'arrive toujours pas au résultat que je veux avoir :

Le garage 1 qui a les outils 1,2,3,4 peut faire les controles :
1 car le ctl a besoin de l'outil 1 et 2
2 car le ctl a besoin de l'outil 1
3 car le ctl a besoin de l'outil 2 et 4
4 car le ctl n'a pas besoin d'outil particulier

Le garage 2 qui a l' outil 3 peut faire les controles :
4 car le ctl n'a pas besoin d'outil particulier

Le garage 3 qui a l' outil 4 peut faire les controles :
4 car le ctl n'a pas besoin d'outil particulier

Le garage 4 qui n'a pas d'outil peut faire les controles :
4 car le ctl n'a pas besoin d'outil particulier

Voilà ce que j'obtiens avec ta requête :
numgarage numctl
1 1
1 2
1 3

**al1_24** · 03/09/2007, 14h34

Il faut alors ajouter les contrôles pour lesquels il ne faut aucun outil...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
    ...
UNION
    SELECT    ga.numgarage
        ,    ct.numctl
    FROM    garage    AS ga
        CROSS JOIN
            controle    AS ct
    WHERE    NOT EXISTS
            (    SELECT    1
                FROM    ctl_outil    AS co
                WHERE    co.numctl    = ct.numctl
            )
;

**Seb981** · 03/09/2007, 14h43

Merci beaucoup !!! C'est exactement le résultat que je voulais avoir.
Pourrais tu me dire a quel niveau je dois préciser mon numéro du garage c'est à dire rajouter numgarage = 1 (ou autre valeur) ?

MERCI
@+ Seb

**al1_24** · 03/09/2007, 14h53

Envoyé par Seb981

Pourrais-tu me dire a quel niveau je dois préciser mon numéro du garage c'est à dire rajouter numgarage = 1 (ou autre valeur) ?

Soit tu ajoutes le filtre sur chaque branche de l'UNION
Soit tu encadres toute la requête et tu ajoutes le filtre sur le résultat:

Code :

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

1
2
3
SELECT *
FROM (...)
WHERE numgarage = 1

**Seb981** · 03/09/2007, 14h56

Ok ! MERCI BEAUCOUP !

@+ Seb

**Seb981** · 03/09/2007, 16h04

J'ai un petit problème lorsque je fais le filtre sur le résultat :

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
 
SELECT *
FROM (
SELECT go.numgarage, co.numctl
FROM garage_outil AS go
    INNER JOIN
        ctl_outil   AS co
        ON  go.numoutil = co.numoutil
WHERE NOT EXISTS
        (   SELECT  1
            FROM    ctl_outil   AS co2
                LEFT JOIN
                    garage_outil AS go2
                    ON  go2.numoutil    = co2.numoutil
                    AND go2.numgarage   = go.numgarage
            WHERE   co2.numctl  = co.numctl
                AND go2.numoutil IS NULL
        )
UNION
    SELECT    ga.numgarage
        ,    ct.numctl
    FROM    garage    AS ga
        CROSS JOIN
            controle    AS ct
    WHERE NOT EXISTS
            (    SELECT    1
                FROM    ctl_outil    AS co
                WHERE    co.numctl    = ct.numctl
            )
)
WHERE numgarage = 1
GROUP BY numgarage

J'ai comme erreur :

Code :

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

1
2
 
#1248 - Every derived table must have its own alias

Pourrais-tu me dire comment la corriger ? J'avais pris la solution 1

Soit tu ajoutes le filtre sur chaque branche de l'UNION

mais au final, il rendait :
déjà les résultats de la premiére requête par ordre croissant
puis ceux de la deuxiéme requête.
ex 1 5 6 8 2 7 9 10 11

**al1_24** · 03/09/2007, 16h12

Envoyé par Seb981

J'ai un petit problème lorsque je fais le filtre sur le résultat :J'ai comme erreur :

Code :

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

1
2
 
#1248 - Every derived table must have its own alias

Pourrais-tu me dire comment la corriger ?

Code :

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

1
2
3
4
5
 
SELECT *
FROM (...
) AS res
WHERE numgarage = 1

Il n'y a pas besoin de GROUP BY puisque le regroupement est déjà fait.

Envoyé par Seb981

J'avais pris la solution 1 mais au final, il rendait :
déjà les résultats de la premiére requête par ordre croissant
puis ceux de la deuxiéme requête.

Pour ordonner le résultat d'une requête, il faut utiliser la clause ORDER BY.
Avec une requête UNION, la clause ORDER BY doit être placée sur la dernière branche de l'UNION et s'applique à toutes les lignes retournées par la requête.

**Seb981** · 04/09/2007, 09h10

Merci pour ton message, et pour tes conseils. En effet je mettais trompé j'avais utilisé GROUP By au lieu de ORDER BY pour ordonner le résultat, il était temps que je me repose ! Donc forcément cela ne pouvais pas marcher.

Avec le code que tu m'a donné j'arrive bien a faire un filtre mais il faudrait aussi que je filtre sur le type du garage.

Dans ma vrai base de données j'ai :

Controle : numctl ...
Controle_TYPE_GARAGE : numctl, numtypegarage
Type_Garage : numtypegarage
Garage : numgarage, numtypegarage

Il faudrait que je rajoute :

Code :

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

1
2
3
4
5
6
SELECT *
FROM (...
) AS res,controle,controle_type_garage, garage
WHERE controle.numctl=controle_type_garage.numctl
AND controle_type_garage.numtypegarage = garage.numtypegarage
AND numgarage = 1

Car pour qu'un controle soit effectué sur un garage il lui fallait les outils mais aussi qu'il puisse aller sur le type de garage.

J'ai essayé de les ajouter dans le code pour filter mais cela me renvoyai n'importe quoi. Pourrais-tu m'aider à faire cette derniére modification.

**Seb981** · 04/09/2007, 10h14

Le meilleur moyen cela aurait été de faire un INTERSECT mais mon mysql phpmyadmin n'en veut pas.

**Seb981** · 05/09/2007, 14h57

Bonjour à tous,

Grâce à l'aide de al1_24, la requête ci-dessous donne bien les contrôles possible à faire sur un garage donné.

Mais j'ai oublié que pour qu'un controle soit réalisable sur un garage, il faut aussi vérifier que le controle technique soit réalisable sur le type du garage.

C'est à dire que si le garage est de type 1, seul les controles qui peuvent faire ce controle sur les garages de type 1 seront pris en compte.

Un controle peut être fait sur plusieurs type de garage.

Un garage a un seul type de garage.

Voici un rappel des tables :

Controle : numctl, codectl ...
Controle_TYPE_GARAGE : numctl, numtypegarage
Type_Garage : numtypegarage
Garage : numgarage, numtypegarage

Si vous voulez m'aider et que ce n'est pas clair ou si vous avez des questions n'hesiter pas à me le signaler.

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
 
SELECT numctl
FROM (
SELECT  go.numgarage
    ,   co.numctl
FROM    garage_outil    AS go
    INNER JOIN
        ctl_outil   AS co
        ON  go.numoutil = co.numoutil
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    ctl_outil   AS co2
                LEFT JOIN
                    garage_outil AS go2
                    ON  go2.numoutil    = co2.numoutil
                    AND go2.numgarage   = go.numgarage
            WHERE   co2.numctl  = co.numctl
                AND go2.numoutil IS NULL
        )
GROUP BY go.numgarage
    ,   co.numctl
UNION
    SELECT    ga.numgarage
        ,    ct.numctl
    FROM    garage    AS ga
        CROSS JOIN
            controle    AS ct
    WHERE    NOT EXISTS
            (    SELECT    1
                FROM    ctl_outil    AS co
                WHERE    co.numctl    = ct.numctl
            )
 
) AS res
WHERE numgarage = 1
ORDER BY numctl

Voici la requête qui pourrait aller si mon sgbd acceptait le INTERSECT :

Code :

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

1
2
3
4
5
6
SELECT ctg.numctl
FROM garage g, controle_type_garage ctg, controle c
WHERE g.numtypegarage = ctg.numtypegarage
AND ctg.numctl = c.numctl
AND numgarage = 1
ORDER BY codectl

**SQLpro** · 05/09/2007, 15h48

INTERSECT n'est autre qu'une jointure !
Transformez vos requêtes en vue et faites une jointure.

A lire : http://sqlpro.developpez.com/cours/s...embles/#L3.5.3

A +

Requête avec deux tables m:m

Langage SQL

Discussions similaires

Partager

Partager