我有 3 个 MySQL 表,我想将它们连接在一起。
课程:
CREATE TABLE `library_classes` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; INSERT INTO `library_classes` (`id`, `name`) VALUES (1, 'Tolkien'), (2, 'CS Lewis');
会话:
CREATE TABLE `library_sessions` ( `id` int NOT NULL AUTO_INCREMENT, `class_id` int NOT NULL, `session_timestamp` timestamp NOT NULL, PRIMARY KEY (`id`), KEY `class_id` (`class_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; INSERT INTO `library_sessions` (`id`, `class_id`, `session_timestamp`) VALUES (1, 1, '2023-08-01 15:30:00'), (2, 2, '2023-08-02 10:15:00'), (3, 1, '2023-08-04 09:30:00');
预订:
CREATE TABLE `library_bookings` ( `id` int NOT NULL AUTO_INCREMENT, `session_id` int NOT NULL, `email` varchar(255) NOT NULL, `datetime_submitted` timestamp NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; INSERT INTO `library_bookings` (`id`, `session_id`, `email`) VALUES (1, 1, 'jose@gmail.com'), (2, 2, 'jane@yahoo.com'), (3, 2, 'john@hotmail.com');
当 jose@gmail.com
登录时,我希望我的页面如下所示:
Tolkien | 2023-08-01 15:30:00 | CANCEL CS Lewis | 2023-08-02 10:15:00 | BOOK NOW Tolkien | 2023-08-04 09:30:00 | BOOK NOW
我将您的数据导入到 SQLFIDDLE;
以下查询将为您提供预期的结果。
它给出的预期输出为: