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
| -- T_ETABLISSEMENT
CREATE TABLE `t_etablissement` (
`id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`nom` VARCHAR(100),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `t_etablissement` (`id`, `nom`) VALUES
(1, "abricot"),
(2, "absinthe"),
(3, "amande"),
(4, "améthyste"),
(5, "anis"),
(6, "argile"),
(7, "arzel"),
(8, "aubergine"),
(9, "avocat"),
(10, "azur");
-- T_MEMBRE
CREATE TABLE `t_membre` (
`login` VARCHAR(20) NOT NULL,
`email` VARCHAR(255) NOT NULL,
PRIMARY KEY (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `t_membre` (`login`, `email`) VALUES
('xxxx', 'xxxx@xxxx.fr'),
('123456', '123456@123456.fr'),
('a', 'ficht@club.fr'),
('abcd', 'abcd@abcd.fr');
-- T_INSCRIPTION
CREATE TABLE `t_inscription` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`login` VARCHAR(20) NOT NULL,
`etablissement_id` TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`login`) REFERENCES `t_membre` (`login`) ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (`etablissement_id`) REFERENCES `t_etablissement` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `t_inscription` (`id`, `login`, `etablissement_id`) VALUES
(1, 'xxxx', 1),
(2, 'xxxx', 5),
(3, 'xxxx', 6),
(4, 'xxxx', 10),
(5, '123456', 1),
(6, '123456', 2),
(7, '123456', 3),
(8, '123456', 4),
(9, 'a', 2),
(10, 'a', 9);
-- REQUETE
SELECT I.id, M.login, M.email, E.id AS id_e, E.nom AS nom_e
FROM t_inscription AS I
INNER JOIN t_membre AS M ON M.login = I.login
INNER JOIN t_etablissement AS E ON E.id = I.etablissement_id
ORDER BY login ASC, id_e ASC; |
Partager