php - MySQL 多连接查询

Question

我有多个表，我试图在单个查询中获取数据。我似乎已经接近解决方案，但似乎无法获得我期望的数据结果。

我的表格示例如下(字段已被截断):

表c
id
姓名
缩写

表 mr(关系表 tat 通过 ID 将表 c 和 m 连接在一起)
id
c.id
m.id

表m
编号

表cnt
id
c.id

表 cmp
id
cnt.id
活跃

我想要的是 C 中的所有字段、M 中的所有字段(其中 m.id = c.id)、CMP 中与 cnt.id 匹配的所有事件 (active = 1) id。

我最近的查询(经过数十次迭代后)是:

SELECT c.id AS id
    , c.name AS name
    , c.abbreviation AS abbr
    , c.active AS active
    , c.last_modified AS last_modified
    , c.modified_by AS modified_by
    , mr.media_id
    , mr.related_object_table
    , mr.related_object_id
    , m.orig_name AS img_name
    , m.unique_name AS img_slug
    , m.file_type AS confed_file_type
    , m.file_size AS file_size
    , COUNT('cmp.id') AS comps
FROM confederations AS c
LEFT JOIN media_relationships AS mr 
ON mr.related_object_id = c.id 
AND mr.related_object_table = 'confederations'
LEFT JOIN media AS m 
ON m.id = mr.media_id
INNER JOIN countries AS cnt
ON cnt.confederations_id = c.id
INNER JOIN competitions AS cmp
ON cmp.countries_id = cnt.id 
AND cmp.active = 1;

我不熟悉联接。

基本上，我期望的结果是:对于每个联盟(表C)，我想要联盟名称、缩写、事件状态(事件)、上次修改日期、修改者；从媒体关系表(表 MR)中，我想要与该联盟关联的图像 id，以便我可以使用该 id 从媒体表 (M) 中获取联盟主图像的图像名称和图像段。

现在我还想要给定联合会的比赛总数(CMP 表)。比赛使用国家/地区 ID 进行存储，该国家/地区 ID 与国家/地区表(表 CNT)中国家/地区的主键 ID 相关联。表 CNT 中的每个国家/地区都有一个联盟 ID。因此，为了获得每个联盟的比赛总数，我“尝试”通过表 CNT 中的 CONFEDERATIONS_ID 获取各自联盟内的所有国家，然后对于每个联盟，我想从表 CMP 中选择所有与国家 ID 组中的 COUNTRIES_ID 匹配的比赛对于那个给定的联盟。 (此时我认为我对如何获得我想要的东西感到困惑)

不知怎的，我得到了正确数量的比赛，但我得到了重复的联合会作为结果。例如，我得到了与此类似的信息(假设我有 3 个不同的联合会，分别有 2、1 和 3 场比赛):

Competitions 1 : name 1 | abbreviation 1 | image 1 | total competitions = 2;
Competitions 1 : name 1 | abbreviation 1 | image 1 | total competitions = 1;
Competitions 1 : name 1 | abbreviation 1 | image 1 | total competitions = 3

我做错了什么？

Answer 1

通过反复试验，我实际上自己解决了这个问题。我回来发布我的答案并看到德根的答案，虽然它的写法与我的不同，但我认为它与我最终的结果非常接近:

SELECT
    cnf.id AS confed_id, cnf.name AS confed_name, cnf.abbreviation AS 
    confed_abbr, cnf.active AS confed_active, cnf.modified_by AS 
    confed_mod_by, cnf.last_modified AS confed_last_mod,
    COUNT(cnt.id) AS total_countries,
    COUNT(cmp.id) AS total_comps,
    mr.media_id, mr.related_object_table, mr.related_object_id, 
    mr.primary_img,
    m.orig_name AS img_name, m.unique_name AS img_slug, m.file_type AS file_type
FROM confederations AS cnf
LEFT JOIN media_relationships AS mr
ON mr.related_object_id = cnf.id AND mr.related_object_table = 'confederations'
LEFT JOIN media AS m
ON m.id = mr.media_id
LEFT JOIN countries AS cnt
ON cnt.confederations_id = cnf.id AND cnt.active = 1
LEFT JOIN competitions AS cmp
ON cmp.countries_id = cnt.id AND cmp.active = 1
GROUP BY cnf.id

到目前为止，这似乎给了我可以使用的结果。我不确定我为所有连接选择的左连接实际上是否为我提供了我需要的一切(看起来是)，以及一旦表变大这是否会省略/添加记录。如果有人能指出我的查询中的问题以及我选择使用左连接而不是像 Degan 那样组合使用左连接和内连接，那将会很有帮助。