mysql - 选择某些标记的帖子及其作者

Question

如何将这 5 个表连接在一起:

tag: id, name
author: username, id
thread_tags: thread_id, tag_id
thread: id, content
author_threads: author_id, thread_id

(我还有一个名为 author_tags (tag_id, author_id) 的表，但我认为这里不需要)。

我想选择所有标有特定标签的线程及其作者。

以下代码返回 #1066 - 不是唯一的表/别名:'tag'

SELECT thread.content, author.username
FROM tag
JOIN thread_tags ON thread.id = thread_tags.thread_id
JOIN tag ON thread_tags.tag_id = tag.id
JOIN author_threads ON author.id = author_threads.author_id
JOIN author ON author_threads.thread_id = thread.id
WHERE tag.name = 'arsenal'

编辑:

这个有效:

SELECT thread.content
FROM tag
JOIN thread_tags ON tag.id = thread_tags.tag_id
JOIN thread ON thread.id = thread_tags.thread_id
WHERE tag.name =  'tagged'
LIMIT 0 , 30

但是，每当我尝试加入作者的主题时，它都会抛出 #1066 错误。

Answer 1

您已经两次加入tag 表，(因此出现错误)并且没有加入thread 表。

SELECT thread.content, author.username
FROM tag
  JOIN thread_tags
    ON tag.id = thread_tags.tag_id
  JOIN thread                                  --join thread (not tag again)
    ON thread.id = thread_tags.thread_id
  JOIN author_threads
    ON author_threads.thread_id = thread.id     --error here too, in your query
  JOIN author
    ON author.id = author_threads.thread_id     --error here too, in your query
WHERE tag.name = 'arsenal'