MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `etudiant` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nom` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `prenom` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `telephone` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `date_naissance` date DEFAULT NULL,
  `mdp` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `photo` longtext COLLATE utf8mb4_unicode_ci,
  `cv` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
INSERT INTO etudiant (id, nom, prenom, email, telephone, mdp) VALUES ('03c71f54-ebab-4044-ae2c-c9c85df542fc', 'XXX_nom', 'XXX_prenom', 'XXX_email', 'XXX_phone', 'XXX_password');
CREATE TABLE `disponibilite` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `etudiant_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `debut` date DEFAULT NULL,
  `fin` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_2CBACE2FDDEAB1A3` (`etudiant_id`),
  CONSTRAINT `FK_2CBACE2FDDEAB1A3` FOREIGN KEY (`etudiant_id`) REFERENCES `etudiant` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
INSERT INTO disponibilite (id, etudiant_id, debut, fin) VALUES ('0aa0731c-e558-4fac-9dfa-0b88bd334beb', '03c71f54-ebab-4044-ae2c-c9c85df542fc', CAST('2022-07-15' AS DATETIME), CAST('2022-07-31' AS DATETIME) ), ('XXXXXXXXXXXXXX-XXXXXX-xxx', '03c71f54-ebab-4044-ae2c-c9c85df542fc', CAST('2022-12-01' AS DATETIME), CAST('2022-12-31' AS DATETIME) );

Records: 2  Duplicates: 0  Warnings: 0


CREATE TABLE `demande` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `etudiant_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `date` datetime NOT NULL,
  `titre` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `contenu` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_2694D7A5DDEAB1A3` (`etudiant_id`),
  CONSTRAINT `FK_2694D7A5DDEAB1A3` FOREIGN KEY (`etudiant_id`) REFERENCES `etudiant` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
INSERT INTO demande(id, etudiant_id, date, titre, contenu) VALUES ('1-XXX-request', '03c71f54-ebab-4044-ae2c-c9c85df542fc', NOW(), '1-titre', '1-contenu'), ('2-XXX-request', '03c71f54-ebab-4044-ae2c-c9c85df542fc', NOW(), '2-titre', '2-contenu')

Records: 2  Duplicates: 0  Warnings: 0


SELECT ET.photo AS photo_editor, CONCAT(ET.prenom, ' ', ET.nom) AS nom_editor, DI.debut, DI.fin, DE.*
FROM etudiant ET
LEFT JOIN disponibilite DI ON DI.etudiant_id = ET.id
INNER JOIN demande DE ON DE.etudiant_id = ET.id
WHERE ET.id='03c71f54-ebab-4044-ae2c-c9c85df542fc'; #AND DI.start < CAST('2022-12-31' AS DATETIME) AND DI.end > CAST('2022-09-11' AS DATETIME)


photo_editor
nom_editor
debut
fin
id
etudiant_id
date
titre
contenu


null
XXX_prenom XXX_nom
2022-07-15
2022-07-31
1-XXX-request
03c71f54-ebab-4044-ae2c-c9c85df542fc
2022-11-08 13:22:03
1-titre
1-contenu

null
XXX_prenom XXX_nom
2022-12-01
2022-12-31
1-XXX-request
03c71f54-ebab-4044-ae2c-c9c85df542fc
2022-11-08 13:22:03
1-titre
1-contenu

null
XXX_prenom XXX_nom
2022-07-15
2022-07-31
2-XXX-request
03c71f54-ebab-4044-ae2c-c9c85df542fc
2022-11-08 13:22:03
2-titre
2-contenu

null
XXX_prenom XXX_nom
2022-12-01
2022-12-31
2-XXX-request
03c71f54-ebab-4044-ae2c-c9c85df542fc
2022-11-08 13:22:03
2-titre
2-contenu

photo_editor	nom_editor	debut	fin	id	etudiant_id	date	titre	contenu
null	XXX_prenom XXX_nom	2022-07-15	2022-07-31	1-XXX-request	03c71f54-ebab-4044-ae2c-c9c85df542fc	2022-11-08 13:22:03	1-titre	1-contenu
null	XXX_prenom XXX_nom	2022-12-01	2022-12-31	1-XXX-request	03c71f54-ebab-4044-ae2c-c9c85df542fc	2022-11-08 13:22:03	1-titre	1-contenu
null	XXX_prenom XXX_nom	2022-07-15	2022-07-31	2-XXX-request	03c71f54-ebab-4044-ae2c-c9c85df542fc	2022-11-08 13:22:03	2-titre	2-contenu
null	XXX_prenom XXX_nom	2022-12-01	2022-12-31	2-XXX-request	03c71f54-ebab-4044-ae2c-c9c85df542fc	2022-11-08 13:22:03	2-titre	2-contenu