同时连接几个表：

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' 

我有follwing表：

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

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' 

Answer 2

你有一个隐含的CROSS JOIN和reply之间thread，author原始查询，并且加入第二次相同的表不走样他们。

使用此：

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 3

要包括的FROM子句中的表，然后加入到他们以及 - 我想你想简单地FROM thread，然后你的加入。

Answer 4

你，说

JOIN author_reply ON thread.id = author_reply.thread_id 

有根据给出的定义在author_reply表中没有thread_id，则该行得到了一个错误。

Answer 5

这工作：

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'