[thelist] MySQL LEFT JOIN & COUNT
Phil Turmel
pturmel-webdev at turmel.org
Fri Mar 13 08:04:19 CDT 2009
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.
More information about the thelist
mailing list