Help with Query

[wallaceoc80]

I am creating a message board and using ASP.NET. In one of my pages I want to display the name of all the boards with the number of threads in the board and the total number of messages in the board.

The tables are as follows:
boards (boardID, title, date_created)

messages(nessageID, title, author, body, boardID, thread, date_created)

The thread column in the messages table keeps track of threads. That is if it is a new thread the value of inserted into the thread column will be 0. If a message is a reply the value entered into thread will be the id of the starting message.

I want to write a query to return the name of all the boards, the number of threads and the total number of messages in each board.

I am able to create a query that returns the names of the boards and the total number of threads in each board as follows:

select b.title BOARD, count(m.messageid) THREADS
from  boards b, messages m
where b.boardid = m.board
AND m.thread = 0
group by b.title;

However, I don't know how to add in to total number of messages. I know this is a long question but any ideas?

Thanks for your help!

Wallace

 

[PHV]

select b.title BOARD, count(*) MESSAGES
from boards b, messages m
where b.boardid = m.board
group by b.title;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

do 'em both in one query

select b.title  as BOARD
     , sum(
         case when m.thread = 0
              then 1 else 0 end
          )     as THREADS
     , count(*) as MESSAGES                
  from boards b
inner
  join messages m
    on b.boardid = m.board
group 
    by b.title

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)