Combine three SQL queries

I'm tryi开发者_StackOverflow社区ng to fetch data for my forum's index. Fetching a list of all the boards, the number of threads in that board, and the number of posts for each of those threads in that board.

SELECT 
board.*, 
IFNULL(a.thread_count, 0) AS thread_count,
b.post_count

FROM 
(SELECT * FROM r_forum_boards ORDER BY position) board 

LEFT OUTER JOIN 
    (SELECT r_forum_threads.board, r_forum_threads.id, 
         COUNT(r_forum_threads.id) AS thread_count 
     FROM r_forum_threads) a 
ON board.id = a.board

LEFT OUTER JOIN
(SELECT r_forum_posts.thread_id, COUNT(*) AS post_count 
 FROM r_forum_posts) b
ON b.thread_id = a.id

The problem is that post_count is returning NULL. I've tried a few different variations of this, but none of them are working.

I'm guessing from the IFNULL that your SQL is MySQL-flavored. In that case, you can use COUNT DISTINCT to simplify things.

SELECT 
board.id,
COUNT(DISTINCT r_forum_threads.id) AS thread_count,
COUNT(r_forum_posts.id) AS post_count
FROM board 
LEFT OUTER JOIN r_forum_threads ON board.id = r_forum_threads.board
LEFT OUTER JOIN r_forum_posts ON r_forum_posts.thread_id = r_forum_threads.id
GROUP BY board.id
ORDER BY board.position

Depending on how much of board.* you actually need, either add columns to the SELECT and GROUP or use this as a subquery to join back to board.

Try putting in a GROUP clause:

LEFT OUTER JOIN 
(SELECT r_forum_threads.board, r_forum_threads.id, COUNT(r_forum_threads.id) AS thread_count FROM r_forum_threads GROUP BY r_forum_threads.id) a 
ON board.id = a.board

LEFT OUTER JOIN
(SELECT r_forum_posts.thread_id, COUNT(*) AS post_count FROM r_forum_posts GROUP BY r_forum_posts.thread_id) b ON b.thread_id = a.id

See if that does the trick.

Perhaps because you are missing the Group By clause in your subqueries? In addition, you do not need the first subquery.

Select board...
    , Coalesce(a.thread_count, 0) AS thread_count
    , b.post_count
From r_forum_boards 
    Left Join   (   
                Select r_forum_threads.board
                    , r_forum_threads.id
                    , Count(r_forum_threads.id) AS thread_count 
                From r_forum_threads
                Group By r_forum_threads.board
                    , r_forum_threads.id
                ) a 
        On a.board = board.id

    Left Join   (
                Select r_forum_posts.thread_id
                    , Count(*) AS post_count 
                From r_forum_posts
                Group By r_forum_posts.thread_id
                ) As b
        On b.thread_id = a.id
Order By r_forum_boards.position

You might consider changing the query slightly to make it easier to test:

Select board...
    , Coalesce(a.thread_count, 0) AS thread_count
    , A.post_count
From r_forum_boards 
    Left Join   (   
                Select r_forum_threads.board
                    , r_forum_threads.id
                    , Count(r_forum_threads.id) AS thread_count 
                    , Posts.post_count
                From r_forum_threads
                    Left Join (
                                Select r_forum_posts.thread_id
                                    , Count(*) AS post_count 
                                From r_forum_posts
                                Group By r_forum_posts.thread_id
                                )  As Posts
                        On Posts.thread_id = r_forum_threads.Id
                Group By r_forum_threads.board
                    , r_forum_threads.id
                ) As A
    On A.board = board.id
    Order By r_forum_boards.position

In this way, you can run the single inner query and ensure you A. get results and B. get values for post_count.

The problem I see is that you're trying to get 2 related but slightly conflicting pieces of data, and probably 2 queries will get you what you need.

You first need a query to get the board names and the number of threads in each board.

  Select Board.*, GroupThread.threadCount
  FROM r_forum_boards Board 
  INNER JOIN (Select board_id, count(*) as threadCount from r_forum_threads group by board_id) GroupThread ON Board.board_id = GroupThread.board_id

Second, for each thread, you need the posts, which are calculated in basically the same way:

  Select Thread.*, GroupPosts.postCount
  FROM r_forum_threads Thread 
  INNER JOIN (Select thread_id, count(*) as postCount from r_forum_posts group by thread_id) GroupPosts ON Thread.board_id = GroupPosts.thread_id

In each of these cases, you look at the parent object, and count the children.