LEFT JOIN 返回应该为 NULL 的非相关数据 [英] LEFT JOIN returning non-related data where it should be NULL
问题描述
我想将 7 张桌子合并在一起.
I have 7 tables I'm trying to join together.
发布
+------+--------+--------+-----------------------+-----------------------+-----------+---------+
| Id | commid | author | created | updated | title | content |
+------+--------+--------+-----------------------+-----------------------+-----------+---------+
| 80 | 16 | 7 | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | Lorem | Ipsum |
| 79 | 16 | 7 | 2019-07-27 23:09:47 | 2019-07-27 23:09:47 | Lorem | Ipsum |
| 78 | 16 | 7 | 2019-07-27 23:09:09 | 2019-07-27 23:09:09 | Lorem | Ipsum |
| 77 | 16 | 7 | 2019-07-27 23:08:36 | 2019-07-27 23:08:36 | Lorem | Ipsum |
| 76 | 16 | 7 | 2019-07-27 23:03:17 | 2019-07-27 23:03:17 | Lorem | Ipsum |
| 75 | 16 | 7 | 2019-07-27 22:38:48 | 2019-07-27 22:38:48 | | |
+------+--------+--------+-----------------------+-----------------------+-----------+---------+
社区
+----+----------------+
| ID | title |
+----+----------------+
| 16 | The Scary Door |
+----+----------------+
社区标签类别
+-----------+----------+
| Community | Category |
+-----------+----------+
| 16 | 5 |
+-----------+----------+
| 16 | 18 |
+-----------+----------+
| 16 | 19 |
+-----------+----------+
| 16 | 20 |
+-----------+----------+
标签类别
+-------------+----+
| category | Id |
+-------------+----+
| character | 5 |
+-------------+----+
| focus | 18 |
+-------------+----+
| warning | 19 |
+-------------+----+
| Time-period | 20 |
+-------------+----+
标签分类
+----------+-----+
| category | Tag |
+----------+-----+
| 5 | 146 |
+----------+-----+
| 19 | 147 |
+----------+-----+
| 20 | 148 |
+----------+-----+
标签
+---------------+-----+
| name | Id |
+---------------+-----+
| spider | 146 |
+---------------+-----+
| arachnophobia | 147 |
+---------------+-----+
| Victorian era | 148 |
+---------------+-----+
发布标签
+------+-----+
| post | Tag |
+------+-----+
| 80 | 146 |
+------+-----+
| 80 | 147 |
+------+-----+
| 80 | 148 |
+------+-----+
有些帖子在某些表格中没有相应的信息，因此我尝试使用 LEFT JOIN.这是我一直在尝试使用的查询:
There are some posts which do no have corresponding information in some of the tables therefore I have tried to use LEFT JOINs. This is the query I have been trying to use:
SELECT p.id, c.id as 'commid', c.title AS 'community', p.author, p.created, p.updated, tc.category, t.name AS 'tag', p.title, p.content
FROM Post p
INNER JOIN Community c
ON p.community=c.id
AND c.id=16
LEFT JOIN CommunityTagCategories cc
ON c.id=cc.community
LEFT JOIN TagCategory tc
ON cc.category=tc.id
LEFT JOIN TagCategorised ct
ON tc.id=ct.category
LEFT JOIN Tag t
ON ct.tag=t.id
LEFT JOIN PostTags pt
ON t.id=pt.tag
GROUP BY p.id, t.id
ORDER BY p.created DESC;
因为只有帖子 80 有任何关联的标签，所以帖子 80 上的三个标签应该有三个条目，其他没有标签的帖子应该有一个条目.像这样.我实际上并不关心带有 NULL 标签的帖子的类别是什么，我只想为这些帖子排一排.
Since only post 80 has any associated tags there should be three entries for the three tags on post 80, and one entry for the other posts with no tags. Like this. I don't actually care what the category is for the posts with NULL tags, I would just like one row for those posts.
我想要的结果
+----+--------+----------------+--------+---------------------+---------------------+-------------+---------------+-------+----------+
| Id | commid | community | author | created | updated | category | tag | title | content |
+----+--------+----------------+--------+---------------------+---------------------+-------------+---------------+-------+----------+
| 80 | 16 | The Scary Door | 7 | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | character | spider | Lorem | Ipsum |
| 80 | 16 | The Scary Door | 7 | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | warning | arachnophobia | Lorem | Ipsum |
| 80 | 16 | The Scary Door | 7 | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | time-period | victorian era | Lorem | Ipsum |
| 79 | 16 | The Scary Door | 7 | 2019-07-27 23:09:47 | 2019-07-27 23:09:47 | | NULL | Lorem | Ipsum |
| 78 | 16 | The Scary Door | 7 | 2019-07-27 23:09:09 | 2019-07-27 23:09:09 | | NULL | Lorem | Ipsum |
| 77 | 16 | The Scary Door | 7 | 2019-07-27 23:08:36 | 2019-07-27 23:08:36 | | NULL | Lorem | Ipsum |
| 76 | 16 | The Scary Door | 7 | 2019-07-27 23:03:17 | 2019-07-27 23:03:17 | | NULL | Lorem | Ipsum |
| 75 | 16 | The Scary Door | 7 | 2019-07-27 22:38:48 | 2019-07-27 22:38:48 | | NULL | | |
+----+--------+----------------+--------+---------------------+---------------------+-------------+---------------+-------+----------+
但是，当我运行查询时，我得到 24 个结果，其中的数据为每个帖子重复 4 行.每个标签都包括 NULL.像这样(但对于每个帖子，不仅仅是我在这里展示的两个帖子).它还显示了一个未与任何帖子一起使用的标签类别 (Focus)，我不确定为什么会出现这种情况.
However, when I run the query I get 24 results with data that repeats 4 rows for each post. with every single tag including NULL. Like this (but with every post, not just the two posts I'm showing here).
It also shows a tag category that is not being used with any posts (Focus) and I'm not sure why that is there.
实际结果
+----+--------+----------------+--------+---------------------+---------------------+-------------+---------------+-------+----------+
| Id | commid | community | author | created | updated | category | tag | title | content |
+----+--------+----------------+--------+---------------------+---------------------+-------------+---------------+-------+----------+
| 80 | 16 | The Scary Door | 7 | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | character | spider | Lorem | Ipsum |
| 80 | 16 | The Scary Door | 7 | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | focus | | Lorem | Ipsum |
| 80 | 16 | The Scary Door | 7 | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | warning | arachnophobia | Lorem | Ipsum |
| 80 | 16 | The Scary Door | 7 | 2019-07-27 23:11:07 | 2019-07-27 23:11:07 | time-period | victorian era | Lorem | Ipsum |
| 79 | 16 | The Scary Door | 7 | 2019-07-27 23:09:47 | 2019-07-27 23:09:47 | character | spider | Lorem | Ipsum |
| 79 | 16 | The Scary Door | 7 | 2019-07-27 23:09:47 | 2019-07-27 23:09:47 | focus | | Lorem | Ipsum |
| 79 | 16 | The Scary Door | 7 | 2019-07-27 23:09:47 | 2019-07-27 23:09:47 | warning | arachnophobia | Lorem | Ipsum |
| 79 | 16 | The Scary Door | 7 | 2019-07-27 23:09:47 | 2019-07-27 23:09:47 | time-period | victorian era | Lorem | Ipsum |
+----+--------+----------------+--------+---------------------+---------------------+-------------+---------------+-------+----------+
推荐答案
经过深思熟虑.我带来了一个应该让你如愿以偿的解决方案结果
After much thinking about it. I came with a solution that should get you the wished Result
Select
p.Id, max(p.author) author, max(p.community) community, max(p.created) created
,max(p.title) title , max(p.content) content
,GROUP_CONCAT(DISTINCT (cttc.category)) category
, GROUP_CONCAT(DISTINCT (ctatc.name)) Tag
From
Post p
inner Join Communnity c On c.Id = p.CommId
right Join (
Select cc.Community, tc.category, tc.Id
From
TagCategory tc
inner join CommunnityTagCategories cc
ON cc.Category = tc.Id) cttc
ON p.Commid = cttc.Community
Left join (
Select
p.post,t.name,ct.category
From
PostTags p
inner join Tag t ON t.Id = p.Tag
inner join TagCategorised ct ON ct.Tag = t.Id
) ctatc
ON ctatc.category = cttc.id and ctatc.post = p.id
GROUP BY p.Id;
它让你
Id 作者社区创建的标题内容类别标签
Id author community created title content category Tag
75 7 The Scary Door 2019-07-27 22:38:48 警告、人物、时间段、焦点
76 7 The Scary Door 2019-07-27 23:03:17 Lorem Ipsum 角色、时间段、焦点、警告
77 7 The Scary Door 2019-07-27 23:08:36 Lorem Ipsum focus,warning,character,Time-period
78 7 The Scary Door 2019-07-27 23:09:09 Lorem Ipsum 警告，人物，时间段，焦点
79 7 The Scary Door 2019-07-27 23:09:47 Lorem Ipsum 角色、时间段、焦点、警告
80 7 The Scary Door 2019-07-27 23:11:07 Lorem Ipsum 警告，人物，时间段，重点蜘蛛恐惧症，蜘蛛，维多利亚时代
75 7 The Scary Door 2019-07-27 22:38:48 warning,character,Time-period,focus
76 7 The Scary Door 2019-07-27 23:03:17 Lorem Ipsum character,Time-period,focus,warning
77 7 The Scary Door 2019-07-27 23:08:36 Lorem Ipsum focus,warning,character,Time-period
78 7 The Scary Door 2019-07-27 23:09:09 Lorem Ipsum warning,character,Time-period,focus
79 7 The Scary Door 2019-07-27 23:09:47 Lorem Ipsum character,Time-period,focus,warning
80 7 The Scary Door 2019-07-27 23:11:07 Lorem Ipsum warning,character,Time-period,focus arachnophobia,spider,Victorian era
这篇关于LEFT JOIN 返回应该为 NULL 的非相关数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
