mysql aggregate difficulties

I have two table structures in my database:

CREATE TABLE `projects` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(32) DEFAULT NULL,
 `description` varchar(128) DEFAULT NULL开发者_Go百科,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

CREATE TABLE `issues` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `member_id` varchar(10) DEFAULT NULL,
 `project_id` int(11) DEFAULT NULL,
 `name` varchar(32) DEFAULT NULL,
 `description` varchar(128) DEFAULT NULL,
 `date_created` date DEFAULT NULL,
 `type` enum('general','bug','requirement') DEFAULT NULL,
 `priority` enum('low','medium','high') DEFAULT NULL,
 `status` enum('resolved','open','discarded') DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `member_id` (`member_id`),
 KEY `project_id` (`project_id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

What I am trying to do is create a select statement that returns the project name, and the number of resolved issues that are associated with that project. I have created the following SQL statement:

select projects.name, count(*) from projects left join issues on projects.id = issues.project_id where status = 'resolved' group by projects.name

However this only returns projects that have at least one resolved issue, I need it to return projects that have 0 resolved issues as well.

It's been a while since I have done any mySQL, can anyone help out? Thanks.

I thought I'd give some more information since I haven't received a working answer yet. If I had four projects and one issue for each project, with two of those issues being 'resolved', I'd expect the query to return:

project_name    | count(*)
--------------------------
first_project   | 1
second_project  | 0
third_project   | 0
fourth_project  | 1

However, the query is only returning projects that have at least one resolved issue.

project_name   | count(*)
-------------------------
first_project  | 1
fourth_project | 1

change your query to:

   SELECT p.name, count(i.*)
     FROM PROJECTS p    
LEFT JOIN ISSUES i ON p.id = i.project_id 
                  AND i.status = 'resolved'
 GROUP BY projects.name

having "status = 'resolved'" in the where clause caused it only to return those projects with at least one issue