recovery and join

Question

For a back-end web project, we use PHP 7.4 and MySQL 8.X, here is the structure of a part of the DB

CREATE TABLE `availability ` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
  `student_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `start` date DEFAULT NULL,
  `end` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_2CBACE2FDDEAB1A3` (`student_id`),
  CONSTRAINT `FK_2CBACE2FDDEAB1A3` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `student` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sex_id` int DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `first_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `picture` longtext COLLATE utf8mb4_unicode_ci,
  `cv` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `IDX_717E22E3448F3B3C` (`sex_id`),
  CONSTRAINT `FK_717E22E3448F3B3C` FOREIGN KEY (`sex_id`) REFERENCES `sex` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `request` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `student_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `date` datetime NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_2694D7A5DDEAB1A3` (`student_id`),
  CONSTRAINT `FK_2694D7A5DDEAB1A3` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Here are the cardinalities:

a STUDENT can have several availabilities (no matter what the category of the availability is) an AVAILABILITY can belong to one or more STUDENTS
a student can have one or more APPLICATIONS
a REQUEST belongs to only one STUDENT

but when I try to retrieve all the REQUESTS according to a period, it retrieves the data twice:

SELECT DE.*, ET.picture AS picture_editor, CONCAT(ET.first_name, ' ', ET.name) AS name_editeur, DI.start, DI.end
FROM request DE
INNER JOIN student ET ON ET.id = DE.student_id
INNER JOIN availability DI ON DI.student_id = DE.student_id
WHERE DI.start< CAST('2022-12-31' AS DATETIME) AND DI.end> CAST('2022-09-11' AS DATETIME)

Can you help me as I want to retrieve the data according to the student's availability

recovery and join

0 Answers