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 and bc_post.user_id = bc_message.message_to where bc_post.post_status = '1' and bc_post.user_id = '1' group by bc_post.post_id
> -----Original Message----- > From: motorpsychkill [mailto:[EMAIL PROTECTED] > Sent: Wednesday, July 23, 2003 11:20 AM > To: mysql > Subject: Difficult count query? > > > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
