Difficult count query?

[motorpsychkill]

I have two tables: bc_post and bc_message. This is what I have so far:

select bc_post.*, count(bc_message.message_to) responses from
bc_post left join
bc_message on
bc_post.post_id = bc_message.message_topostid
where
bc_post.post_status = '1' and
bc_post.user_id = '1'
group by
bc_post.post_id
This gets correct posts but not correct responses. I want to limit the
responses (the count) to only those where
bc_message.message_to = bc_post.user_id. If I add that statement, I get the
correct responses but I lose any posts
that do not have any messages (which I do not want to do). How do I
retrieve the count (responses) and return 0 if there aren't any?

CREATE TABLE bc_post (
post_id int(10) unsigned NOT NULL auto_increment,
post_date date NOT NULL default '0000-00-00',
post_details text NOT NULL,
post_message text NOT NULL,
post_gender1 varchar(7) NOT NULL default '',
post_gender2 varchar(7) NOT NULL default '',
user_id varchar(25) NOT NULL default '',
post_ip varchar(20) NOT NULL default '',
post_timestamp timestamp(14) NOT NULL,
post_status int(1) NOT NULL default '0',
postgeo_id char(3) NOT NULL default '',
PRIMARY KEY (post_id)
) TYPE=MyISAM;
CREATE TABLE bc_message (
message_id int(10) unsigned NOT NULL auto_increment,
message_topostid int(10) NOT NULL default '0',
message_from varchar(25) NOT NULL default '',
message_to varchar(25) NOT NULL default '',
message_message text NOT NULL,
message_ip varchar(20) NOT NULL default '',
message_timestamp timestamp(14) NOT NULL,
message_status varchar(4) NOT NULL default '0',
PRIMARY KEY (message_id)
) TYPE=MyISAM;

Thank you for any help!!!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw