Another SQL Query

[wallaceoc80]

I have another SQL query that I'm having trouble with.

I have a board table and a messages table

board(boardID, title, date)

messages(messageID, title, author, body, board, thread, date_created)

The table keeps track of messages in a message board. The way I keep track to see whether a message is a thread starting message or a reply to a messages is through the thread column.

If the message is starting a new thread then the value 0 is inserted into the colum. If it is a reply to a certain thread then the messageID of the starting message.

I want to write a query that will return all threads that belong to one particular board (say boardID 1). All I want to return is the messageID of the original starting message of the thread, the author of the original starting message of the thread and the total number of replies for the thread!

How can I do this? Do I use a self-join for on the messageID column?

Please help!!

Thanks,

Wallace

 

[wallaceoc80]

I just read through my post and it might be a little unclear so hopefully this will clear it up:

I have a SQL query that I'm having trouble with.

I have a board table and a messages table

board(boardID, title, date)

messages(messageID, title, author, body, board, thread, date_created)

The message table keeps track of messages in a message board. The way I keep track to see whether a message is a thread starting message or a reply to a messages is through the thread column.

If the message is starting a new thread then the value 0 is inserted into the thread colum. If it is a reply to a certain thread then the messageID of the starting message is inserted into the thread column.

I want to write a query that will return all threads that belong to one particular board (say boardID = 1). All I want to return is the messageID of the original starting message of the thread, the author of the original starting message of the thread and the total number of replies for the thread!

How can I do this? Do I use a self-join for on the messageID column?

Please help!!

Thanks,

Wallace

 

[rac2]

Do I use a self-join for on the messageID column?"

Well that would, at the least, be

SELECT *
FROM messages a
JOIN messages b ON
      b.messageID = a.messageID

But let me think about the data you wish to retrieve.


The number of messages and replies per thread would be

SELECT thread,
       ( COUNT(*) + 1 ) AS "nMessages",
       COUNT(*) AS "nReplies" 
FROM messages
WHERE thread <> 0
GROUP BY thread

"the messageID of the original starting message of the thread, the author "
That would be the messageID and author of the starting messages.

SELECT messageID, author
FROM messages
WHERE thread = 0

Putting these together in one query -

SELECT m.messageID, m.author, x.nReplies
FROM messages m
JOIN (
       SELECT thread,
              ( COUNT(*) + 1 ) AS "nMessages",
              COUNT(*) AS "nReplies" 
       FROM messages
       WHERE thread <> 0
       GROUP BY thread
     ) x ON x.thread = m.messageID
WHERE m.thread = 0

So, no, this would not use a self-join.