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'

Answer 2

You had an implicit CROSS JOIN between thread, author and reply in your original query, and joined the same tables for the second time without aliasing them.

Use this:

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

You are including tables in the FROM clause and then joining to them as well - I think you want simply FROM thread, and then your joins.

Answer 4

You got an error on the row that says

JOIN author_reply ON thread.id = author_reply.thread_id

there is no thread_id in your author_reply table according to the definitions given.

Answer 5

This works:

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'