mysql - 多条SQL连接困难查询

Question

我有一个奇怪的模式，但当它被设计出来时，它在当时似乎是个好主意。我有一个 master 表，lesson_objects，它有链接到词汇表、视频表和测验表的外键。

词汇表:

CREATE TABLE `se_vocab` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `vocab_word` VARCHAR(255) NOT NULL,
    `vocab_audio` INT(10) NULL DEFAULT '0',
    `vocab_image` INT(10) NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)

视频表:

CREATE TABLE `se_video` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `video_name` VARCHAR(255) NOT NULL,
    `video_description` MEDIUMTEXT NOT NULL,
    `video_file_name` VARCHAR(50) NULL DEFAULT NULL,
    `video_url` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`)
)

测验表:

CREATE TABLE `se_quizzes` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `quiz_name` VARCHAR(80) NOT NULL,
    `quiz_description` TINYTEXT NULL,
    PRIMARY KEY (`id`)
)

类(class)对象(包含之前表格的外键)

CREATE TABLE `se_lesson_org` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `lesson_id` INT(10) NOT NULL,
    `section_object_type` ENUM('video','vocabulary','quiz') NOT NULL,
    `section_object_id` INT(10) NOT NULL,
    PRIMARY KEY (`id`)
)

我正在尝试创建一个查询，该查询返回来自 lesson_objects 的所有记录，但也包括该记录中类型(词汇等)的列中的数据

例如:

只有我的查询不返回任何行，而理想情况下它应该转换多行，每条记录都包含一些空列。例如。如果不是词汇，则测验和视频栏将为空。

我的尝试非常糟糕，但为了指导起见，这里有一个:

SELECT 
    lo.id, lo.section_object_type, lo.section_object_id, 
    vo.id, vo.vocab_text, vo.vocab_image, vo.vocab_audio
    vi.id, vi.video_name, vi.video_url,
    q.id, q.quiz_name
    FROM se_lesson_org lo, se_vocab vo, se_video vi, se_quizzes q
    WHERE lo.section_object_id = vo.id 
    OR lo.section_object_id = vi.id
    OR lo.section_object_id = q.id

如有任何帮助/意见，我们将不胜感激。谢谢。

Answer 1

使用 LEFT JOIN 返回 se_lesson_org 中的所有行。此外，添加一个 JOIN 条件以匹配特定的 section_object_type

SELECT
    lo.*,
    vo.*,
    vi.*,
    q.*
FROM se_lesson_org lo
LEFT JOIN se_vocab vo
    ON vo.id = lo.section_object_id
    AND lo.section_object_type = 'vocabulary'
LEFT JOIN se_video vi
    ON vi.id = lo.section_object_id
    AND lo.section_object_type = 'video'
LEFT JOIN se_quizzes q
    ON q.id = lo.section_object_id
    AND lo.section_object_type = 'quiz'

---

注意:避免使用旧式 JOIN 语法。阅读此 article作者:Aaron Bertrand。