Difficulté sur 2 requêtes avec jointures

**Tchupacabra** · 04/12/2009, 18h54

Bonjour à toutes et à tous,

je n'arrive pas à concevoir mes requêtes.
Peut être à cause de la modélisation de la base... je sais pas.
Règles:
-Une personne peut travailler dans plusieurs établissements
-Un service est propre à un établissement

Voici la base test :

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
 
-- T_ETABLISSEMENT
 
CREATE TABLE `t_etablissement` (
  `id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `t_etablissement` (`id`, `nom`) VALUES
(1, "E1"),
(2, "E2"),
(3, "E3"),
(4, "E4"),
(5, "E5"),
(6, "E6");
 
-- T_SERVICE
 
CREATE TABLE `t_service` (
  `id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(100) NOT NULL,
  `id_etablissement` SMALLINT(5) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`id_etablissement`) REFERENCES `t_etablissement` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `t_service` (`id`, `nom`, `id_etablissement`) VALUES
(1, "S1 pour E1", 1),
(2, "S2 pour E4", 4),
(3, "S3 pour E4", 4);
 
-- T_PERSONNE
 
CREATE TABLE `t_personne` (
  `id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `civilite` ENUM("","Mr","Mme","Mlle") NOT NULL,
  `nom` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `t_personne` (`id`, `civilite`, `nom`) VALUES 
(1, "Mr", "P1"),
(2, "Mlle", "P2"),
(3, "Mr", "P3"),
(4, "Mr", "P4"),
(5, "Mr", "P5"),
(6, "Mr", "P6"),
(7, "Mme", "P7"),
(8, "Mme", "P8"),
(9, "Mr", "P9"),
(10, "Mr", "P10");
 
-- T_APPARTENANCE
 
CREATE TABLE `t_appartenance` (
  `id_personne` SMALLINT(5) UNSIGNED NOT NULL,
  `id_etablissement` SMALLINT(5) UNSIGNED NOT NULL,
  `id_service` SMALLINT(5) UNSIGNED,
  `statut` VARCHAR(50) DEFAULT "",
  `tel` VARCHAR(10) DEFAULT "",
  PRIMARY KEY  (`id_personne`, `id_etablissement`),
  FOREIGN KEY (`id_personne`) REFERENCES `t_personne` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  FOREIGN KEY (`id_etablissement`) REFERENCES `t_etablissement` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  FOREIGN KEY (`id_service`) REFERENCES `t_service` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `t_appartenance` (`id_personne`, `id_etablissement`, `id_service`, `statut`, `tel`) VALUES 
(1, 1, 1, "E1-S1 -> Directeur Général", "0101010101"),
(2, 1, 1, "E1-S1 -> Assistante de direction", "0202020202"),
(3, 1, NULL, "E1 -> Informaticien", "0303030303"),
(3, 2, NULL, "E2 -> Informaticien", "0303030303"),
(6, 4, 2, "E4-S2 -> Directeur", "0606060606"),
(7, 4, 3, "E4-S3 -> Responsable", "0707070707"),
(8, 4, NULL, "E4 -> Secrétaire", "0808080808"),
(9, 4, 3, "E4-S3 -> Stagiaire", "0909090909"),
(10, 6, NULL, "E6 -> Stagiaire", "1010101010");

J'aimerais par exemple obtenir :
1/ la liste des personnes du service S3 avec toutes les infos dispo.
requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT 
	E.nom AS e_nom, S.nom AS s_nom, 
	P.id AS p_id, P.nom AS p_nom, 
	A.statut AS a_statut, A.tel AS a_tel
FROM 
	t_etablissement E
		INNER JOIN t_service S ON S.id_etablissement = E.id
		LEFT JOIN t_appartenance A ON A.id_etablissement = E.id
		LEFT JOIN t_personne P ON P.id = A.id_personne
WHERE 
	S.id = 3;

ce qui donne :

Code :

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

1
2
3
4
E4  	S3 pour E4  	6  	P6  	E4-S2 -> Directeur  	0606060606
E4 	S3 pour E4 	7 	P7 	E4-S3 -> Responsable 	0707070707
E4 	S3 pour E4 	8 	P8 	E4 -> Secrétaire 	0808080808
E4 	S3 pour E4 	9 	P9 	E4-S3 -> Stagiaire 	0909090909

au lieu de :

Code :

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

1
2
E4 	S3 pour E4 	7 	P7 	E4-S3 -> Responsable 	0707070707
E4 	S3 pour E4 	9 	P9 	E4-S3 -> Stagiaire 	0909090909

et de la même manière :
2/ la liste des personnes travaillant à E1
requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT 
	E.nom AS e_nom, S.nom AS s_nom, 
	P.id AS p_id, P.nom AS p_nom, 
	A.statut AS a_statut, A.tel AS a_tel
FROM 
	t_etablissement E
		INNER JOIN t_service S ON S.id_etablissement = E.id
		LEFT JOIN t_appartenance A ON A.id_etablissement = E.id
		LEFT JOIN t_personne P ON P.id = A.id_personne
WHERE 
	E.id = 1;

ce qui donne :

Code :

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

1
2
3
E1  	S1 pour E1  	1  	P1  	E1-S1 -> Directeur Général  	0101010101
E1 	S1 pour E1 	2 	P2 	E1-S1 -> Assistante de direction 	0202020202
E1 	S1 pour E1 	3 	P3 	E1 -> Informaticien 	0303030303

au lieu de :

Code :

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

1
2
3
E1  	S1 pour E1  	1  	P1  	E1-S1 -> Directeur Général  	0101010101
E1 	S1 pour E1 	2 	P2 	E1-S1 -> Assistante de direction 	0202020202
E1 	NULL 		3 	P3 	E1 -> Informaticien 	0303030303

Quelqu'un pourrait-il m'aider SVP ?
Merci d'avance

**fsmrel** · 05/12/2009, 00h05

Liste des personnes du service S3 avec toutes les infos dispo.

Il faudrait déjà établir une jointure entre les tables t_appartenance et t_service :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
	E.nom AS e_nom, S.nom AS s_nom, 
	P.id AS p_id, P.nom AS p_nom, 
	A.statut AS a_statut, A.tel AS a_tel
FROM 
	t_etablissement E
		INNER JOIN t_service S ON S.id_etablissement = E.id
		LEFT JOIN t_appartenance A ON A.id_etablissement = E.id  
                  AND A.id_service = S.id
		LEFT JOIN t_personne P ON P.id = A.id_personne
WHERE 
	S.id = 3;

Ensuite, puisque le service est obligatoirement attaché à un établissement et qu'une appartenance est obligatoirement attachée à une personne et à un établissement, vous pouvez remplacer chaque outer join par une jointure naturelle :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT 
	E.nom AS e_nom, S.nom AS s_nom, 
	P.id AS p_id, P.nom AS p_nom, 
	A.statut AS a_statut, A.tel AS a_tel
FROM 
	t_etablissement E
		INNER JOIN t_service S ON S.id_etablissement = E.id
		INNER JOIN t_appartenance A ON A.id_etablissement = E.id 
                  AND A.id_service = S.id
		INNER JOIN t_personne P ON P.id = A.id_personne
WHERE 
	S.id = 3;

___________________

Liste des personnes travaillant à E1

On conserve les jointures naturelles entre les tables t_etablissement, t_appartenance et t_personne, En revanche, la jointure avec t_service doit être faite en relation avec t_appartenance, puisque c’est dans cette table que l’attribut id service peut être marqué NULL (et du fait de NULL, cette fois-ci il faut utiliser un outer join, il a été inventé pour ça).

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT 
	E.nom AS e_nom, S.nom AS s_nom, 
	P.id AS p_id, P.nom AS p_nom, 
	A.statut AS a_statut, A.tel AS a_tel
FROM 
	t_etablissement E
		INNER JOIN t_appartenance A ON A.id_etablissement = E.id
		INNER JOIN t_personne P ON P.id = A.id_personne
		LEFT JOIN t_service S ON S.id_etablissement = A.id_service
WHERE 
	E.id = 1;

**Tchupacabra** · 07/12/2009, 15h14

Un GRAND MERCI à toi fsmrel !

ça marche nickel...

Difficulté sur 2 requêtes avec jointures

Langage SQL

Discussions similaires

Partager

Partager