一次连接多个表：

Question

我正在使用以下 SQL 语句：

SELECT reply.id, reply.content, author.username
FROM thread, reply, author
JOIN thread_reply ON thread.id = thread_reply.thread_id
JOIN reply ON thread_reply.reply_id = reply.id
JOIN author_reply ON thread.id = author_reply.thread_id
JOIN author ON author_reply.author_id = author.id
WHERE thread.id = '40'

我有以下表：

thread_reply: thread_id, reply_id

reply: id, content, created (timestamp)

author: id, username, password_hash, salt #etc

thread: id, content, created

author_reply: author_id, reply_id

我不断收到以下错误：

#1066 - Not unique table/alias: 'reply'

哦，我正在使用 MySQL。

Answer 1

您将表包含在 FROM 子句中，然后也加入它们 - 我认为您只需要 FROM thread，然后进行联接。

Answer 2

您在该行上收到一个错误，表明

JOIN author_reply ON thread.id = author_reply.thread_id

根据给定的定义，您的author_reply 表中没有thread_id。

Answer 3

这有效：

SELECT reply.id, reply.content, author.username
FROM thread
JOIN thread_reply ON thread.id = thread_reply.thread_id
JOIN reply ON thread_reply.reply_id = reply.id
JOIN author_reply ON reply.id = author_reply.reply_id
JOIN author ON author_reply.author_id = author.id
WHERE thread.id = '40'

Answer 4

您在原始查询中的 thread、author 和 reply 之间有隐式的CROSS JOIN，并联接了相同的表第二次没有别名。

使用这个：

SELECT  reply.id, reply.content, author.username
FROM    thread t
JOIN    thread_reply tr
ON      tr.thread_id = t.id
JOIN    reply r
ON      r.id = tr.reply_id
JOIN    author_reply ar
ON      ar.reply_id = r.id
JOIN    author a
ON      a.id = ar.author_id
WHERE   thread.id = '40'

Answer 5

SELECT reply.id, reply.content, author.username
FROM thread
INNER JOIN thread_reply ON thread.id = thread_reply.thread_id
INNER JOIN reply ON thread_reply.reply_id = reply.id
INNER JOIN author_reply ON thread.id = author_reply.thread_id
INNER JOIN author ON author_reply.author_id = author.id
WHERE thread.id = '40'