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/[EMAIL PROTECTED]
