mysql - 从三个表的多路连接中有效过滤重复项

Question

我有三个具有许多:许多关系的表，如下所示:

Tag
+-------+---------+
| TagID | ThemeID |
+-------+---------+
| t1    | th1     |
+-------+---------+
| t2    | th2     |
+-------+---------+
| t3    | th3     |
+-------+---------+
| t4    | th1     |
+-------+---------+
| t5    | th2     |
+-------+---------+

Theme
+---------+------------+
| ThemeID | ThemeStuff |
+---------+------------+
| th1     | ts1        |
+---------+------------+
| th2     | ts2        |
+---------+------------+
| th3     | ts3        |
+---------+------------+
| th4     | ts4        |
+---------+------------+

UserTag
+-------+--------+
| TagID | UserID |
+-------+--------+
| t1    | Fred   |
+-------+--------+
| t2    | Fred   |
+-------+--------+
| t4    | Fred   |
+-------+--------+
| t4    | Frank  |
+-------+--------+

给定一个特定的 UserID，我尝试检索这些标签的关联主题记录，并保留一个相关的 TagID 值作为我如何获取该主题记录的引用。

我开始的简单方法是:

    SELECT a.TagID, c.ThemeID, c. Themestuff FROM UserTags a, Tags b, Theme c 
        WHERE a.UserID = 'Fred' AND a.TagID = b.TagID AND b.ThemeID = c.ThemeID

问题在于，如果 Fred 在一周内使用不同的标签访问它们，我会多次获得包含相同主题记录的 Fred 结果:

+------------------------------+
| Simple Results               |
+-------+---------+------------+
| TagID | ThemeID | ThemeStuff |
+-------+---------+------------+
| t1    | th1     | ts1        |
+-------+---------+------------+
| t2    | th2     | ts2        |
+-------+---------+------------+
| t4    | th1     | ts1        |
+-------+---------+------------+

我只想看到一个匹配的主题记录，与映射到它的 TagID 之一 - 我不关心映射到它的特定 TagID，只要我其中之一与该用户相关联。

换句话说，目标结果应该是这样的:

+------------------------------+
| Target  Results              |
+-------+---------+------------+
| TagID | ThemeID | ThemeStuff |
+-------+---------+------------+
| t1    | th1     | ts1        |
+-------+---------+------------+
| t2    | th2     | ts2        |
+-------+---------+------------+ 
or
+------------------------------+
| Target  Results              |
+-------+---------+------------+
| TagID | ThemeID | ThemeStuff |
+-------+---------+------------+
| t4    | th1     | ts1        |
+-------+---------+------------+
| t2    | th2     | ts2        |
+-------+---------+------------+

我尝试在嵌套子查询上使用 DISTINCT 对其进行过滤，但它很快就变得一团糟，但仍然不太正确。我觉得一定有我缺少的简单解决方案。希望有任何见解...

Answer 1

如果您不希望主题重复，那么我建议exists:

select th.*
from themes th
where exists (select 1
              from tags t join
                   usertags ut
                   on tu.tagid = t.tagid
              where t.themeid = th.themeid and
                    u.userid = 'Fred'
             );