Joining Several tables at once:

Question

I'm using the following SQL statement:

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'

I have the follwing tables:

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

I keep getting the following error:

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

Oh and I'm using 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'