Mysql连接三个表的数据(多对多关系)

Question

这是我的 mysql 数据库架构:

我想创建查询，以以下方式返回有关所有训练的数据:

training.*, [type.type], [voivodeship.name]如果没有与给定训练相关的类型或省份，则应在列值中返回 null。

例如:

 {
    "id": 1,
    "name": "Example training 2",
    "description": "This is a description 2",
    "status": "status 2",
    "registerFrom": null,
    "registerTo": null,
    "DateOfInsert": "2018-02-06T12:00:57.000Z",
    "training types": "Example type 1,Example type 3,Example type 2"
    "localizations": "loc 1, loc 3"
 },
...
 {
    "id": 99,
    "name": "Example training 99",
    "description": "This is a description 99",
    "status": "status 2",
    "registerFrom": null,
    "registerTo": null,
    "DateOfInsert": "2018-02-06T12:00:57.000Z",
    "training types": null,
    "localizations": null
 },
 {
    "id": 99,
    "name": "Example training 99",
    "description": "This is a description 99",
    "status": "status 2",
    "registerFrom": null,
    "registerTo": null,
    "DateOfInsert": "2018-02-06T12:00:57.000Z",
    "training types": "Example type 9,Example type 4,Example type 2",
    "localizations": "loc 56, loc 32"
  },

这是我当前的查询，它返回带有有关其本地化信息的培训(遗憾的是，它不会返回没有本地化信息的培训)，而且我也不知道如何修改它以返回所有类型:

SELECT `training`.*, GROUP_CONCAT(`voivodeship`.`name`) AS `Localizations`
FROM `training_localization` 
INNER JOIN `training` ON (`training_localization`.`training_id` = `training`.`id`) 
INNER JOIN `voivodeship` ON (`training_localization`.`voivodeship_id` = `voivodeship`.`id`) 
GROUP BY `training`.`id`

我对sql不太有经验。是否可以通过一个查询来实现？

根据戈登的回答，我提出了新的查询，看起来它正在工作:):

SELECT t.*, GROUP_CONCAT(vs.name) AS Localizations,  tw.types AS types
FROM training t 
     LEFT JOIN training_localization tl ON tl.training_id = t.id 
     LEFT JOIN voivodeship vs ON tl.voivodeship_id = vs.id
     LEFT JOIN(
        SELECT t.*, GROUP_CONCAT(ty.type) AS Types
        FROM training t 
             LEFT JOIN training_type tt ON tt.training_id = t.id 
             LEFT JOIN type ty ON tt.type_id = ty.id
        GROUP BY t.id
        ) tw ON tw.id = t.id
GROUP BY t.id;

Answer 1

如果您想要全部内容，请考虑“外部连接”。如果您想要所有训练，那应该是一系列左连接中的第一个表:

SELECT t.*, GROUP_CONCAT(vs.name) AS Localizations
FROM training t LEFT JOIN
     training_localization tl
     ON tl.training_id = t.id LEFT JOIN 
     voivodeship vs
     ON tl.voivodeship_id = vs.id
GROUP BY t.id;

注释:

• LEFT JOIN 保留第一个表中的所有内容以及后续表中的匹配行(除非您使用内部联接或 where 子句撤消它)。
• 表别名使查询更易于编写和阅读。
• 消除不必要的反引号使查询更易于编写和阅读。