[thelist] MySQL LEFT JOIN & COUNT

[Phil Turmel]

Hi Marc,

Marc Lough wrote:
> I'm having some problems with MySQL using LEFT JOIN and COUNT..
> 
> I'm currently executing the following MySQL Query:
> 
>             SELECT cat.forum_category_name AS cat_name,
>                     board.forum_board_id AS board_id, board.forum_board_name
> AS board_name
>                 FROM forum_category AS cat
>             LEFT JOIN forum_board AS board
>                 ON cat.forum_category_id = board.forum_category_id
> 
> I need to get two COUNT()'s showing the amount of topics & posts for each
> board.
> 
> Btw, if the post is a topic, forum_post_topic would be set to 1.
> 

Something like this ought to do (untested):

SELECT
   cat.forum_category_name AS cat_name,
   board.forum_board_id AS board_id,
   board.forum_board_name AS board_name,
   SUM(post.forum_post_topic) AS topics,
   COUNT(post.forum_post_topic) AS posts
FROM forum_category AS cat
   LEFT JOIN forum_board AS board
     ON cat.forum_category_id = board.forum_category_id
   LEFT JOIN forum_post AS post
     ON post.forum_board_id = board.forum_board_id
GROUP BY
   cat.forum_category_name,
   board.forum_board_id,
   board.forum_board_name

> Is this possible to do? thanks in advance.
> 
[/snip]

HTH,

Phil
-- 
Need to contact me offlist?
   Drop -webdev or you probably won't get through.