PHP|Débutant :: Forums

--
-- Structure de la table 'jours'
--

DROP TABLE IF EXISTS jours;
CREATE TABLE IF NOT EXISTS jours (
  idj int(3) NOT NULL AUTO_INCREMENT,
  jour date NOT NULL,
  PRIMARY KEY (idj),
  UNIQUE KEY jour (jour)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Contenu de la table 'jours'
--

INSERT INTO jours (idj, jour) VALUES
(1, '2011-01-01'),
(2, '2011-01-02'),
(3, '2011-01-03'),
(4, '2011-01-04'),
(5, '2011-01-05'),
(6, '2011-01-06'),
(7, '2011-01-07');
-- --------------------------------------------------------

--
-- Structure de la table 'classes'
--

DROP TABLE IF EXISTS classes;
CREATE TABLE IF NOT EXISTS classes (
  idc int(2) NOT NULL AUTO_INCREMENT,
  classe varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (idc)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Contenu de la table 'classes'
--

INSERT INTO classes (idc, classe) VALUES
(1, 'classe1'),
(2, 'classe2');

-- --------------------------------------------------------

--
-- Structure de la table 'etudiants'
--

DROP TABLE IF EXISTS etudiants;
CREATE TABLE IF NOT EXISTS etudiants (
  ide int(2) NOT NULL AUTO_INCREMENT,
  nom varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (ide)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Contenu de la table 'etudiants'
--

INSERT INTO etudiants (ide, nom) VALUES
(1, 'etudiant1'),
(2, 'etudiant2'),
(3, 'etudiant3'),
(4, 'etudiant4'),
(5, 'etudiant5'),
(6, 'etudiant6'),
(7, 'etudiant7'),
(8, 'etudiant8'),
(9, 'etudiant9'),
(10, 'etudiant10'),
(11, 'etudiant11'),
(12, 'etudiant12'),
(13, 'etudiant13'),
(14, 'etudiant14'),
(15, 'etudiant15');

-- --------------------------------------------------------

--
-- Structure de la table 'jointables'
--

DROP TABLE IF EXISTS jointables;
CREATE TABLE IF NOT EXISTS jointables (
  idetudiant int(11) NOT NULL,
  idjour int(11) NOT NULL,
  idclasse int(11) NOT NULL,
  PRIMARY KEY (idetudiant,idjour,idclasse)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Contenu de la table 'jointables'
--

INSERT INTO jointables (idetudiant, idjour, idclasse) VALUES
(1, 1, 1),
(1, 3, 1),
(2, 1, 1),
(3, 2, 1),
(4, 2, 1),
(5, 3, 1),
(6, 3, 1),
(10, 1, 2),
(10, 3, 2),
(11, 1, 2),
(11, 3, 2),
(12, 2, 2),
(12, 3, 2),
(14, 2, 2);

-- --------------------------------------------------------

SELECT classes.classe, jours.jour, GROUP_CONCAT(etudiants.nom) AS liste
FROM joinTables
INNER JOIN etudiants ON joinTables.idetudiant = etudiants.ide
INNER JOIN jours ON joinTables.idjour = jours.idj
INNER JOIN classes ON joinTables.idclasse = classes.idc
GROUP BY classes.classe, jours.jour