SQL - 基本内连接查询
P粉959676410
P粉959676410 2023-09-11 11:02:31
0
1
532

我有 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

P粉959676410
P粉959676410

全部回复(1)
P粉060528326

我将您的数据导入到 SQLFIDDLE;

以下查询将为您提供预期的结果。

select lc.name, ls.session_timestamp,
if(lb.id is null, 'BOOK NOW', 'CANCEL') as booking_status
from library_classes lc
join library_sessions ls on lc.id = ls.class_id
left join library_bookings lb on ls.id = lb.session_id and lb.email = 'jose@gmail.com'
order by ls.session_timestamp;

它给出的预期输出为:

name        session_timestamp       booking_status
Tolkien     2023-08-01T15:30:00Z    CANCEL
CS Lewis    2023-08-02T10:15:00Z    BOOK NOW
Tolkien     2023-08-04T09:30:00Z    BOOK NOW
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责声明 Sitemap
PHP中文网:公益在线PHP培训,帮助PHP学习者快速成长!