[thelist] MySQL LEFT JOIN & COUNT

[Marc Lough]

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.

Is this possible to do? thanks in advance.

Below is the table structure:

-- --------------------------------------------------------

--
-- Table structure for table `forum_category`
--

CREATE TABLE `forum_category` (
  `forum_category_id` int(10) unsigned NOT NULL auto_increment,
  `forum_category_name` varchar(32) NOT NULL,
  `forum_category_weight` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`forum_category_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `forum_board`
--

CREATE TABLE `forum_board` (
  `forum_board_id` int(10) unsigned NOT NULL auto_increment,
  `forum_category_id` int(10) unsigned NOT NULL,
  `forum_board_name` varchar(32) NOT NULL,
  PRIMARY KEY  (`forum_board_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `forum_post`
--

CREATE TABLE `forum_post` (
  `forum_post_id` int(10) unsigned NOT NULL auto_increment,
  `forum_board_id` int(10) unsigned NOT NULL,
  `forum_post_subject` varchar(128) NOT NULL,
  `forum_post_message` text NOT NULL,
  `forum_post_topic` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`forum_post_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------