mysql - 一次连接多个表 :

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

您在原始查询中的 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'