Condition in GROUP_CONCAT ()

Question

Guys I have the following tables in my DB

Places

• id (primary key)
• name

Projects

• id (primary key)
• location_id (foreign key => locations)
• project_name

milestones

• id (primary key)
• name

statuses

• id (primary key)
• Status

project_milestones

• id (primary key)
• project_id (foreign key => projects)
• milestone_id (foreign key => stages)
• status_id (foreign key => statuses)

Here, to list all stages (having status 9) of all projects, I use the following query

SELECT projects.projectname, locations.name, DATE_FORMAT(projects.created_at, '%d-%m-%y') as projectdate, GROUP_CONCAT(milestones.name ORDER BY project_milestones.milestone_id ASC separator '<br/>') AS milestones FROM projects INNER JOIN locations ON projects.location_id=locations.id INNER JOIN project_milestones ON project_milestones.project_id=projects.id AND project_milestones.status_id=9 INNER JOIN milestones ON project_milestones.milestone_id=milestones.id INNER JOIN statuses ON project_milestones.status_id=statuses.id AND project_milestones.milestone_id=milestones.id GROUP BY projects.projectname 

The result is as follows

  Location Date Created Project Name Milestones (Status 9)
 Bangalore 10-25-11 ABCD CSO Contacts, Developer
 Bangalore 11-11-11 Friday Establish, Publish list
 Bangalore 08-11-11 XX CSO Contacts, Assemble, Layouts

It works fine, but I need another GROUP_CONCAT column: Main stages (state 10) to display all stages (which have status 10) of all projects.

Conclusion, like all projects with [State Milestones-9] and [Status Milestones-10]. How can i do this?

Answer 1

 SELECT *, ( SELECT GROUP_CONCAT(m.name ORDER BY m.id) FROM project_milestones pm JOIN milestones m ON m.id = pm.milestone_id WHERE pm.project_id = p.id AND pm.status_id = 9 ) milestone_9, ( SELECT GROUP_CONCAT(m.name ORDER BY m.id) FROM project_milestones pm JOIN milestones m ON m.id = pm.milestone_id WHERE pm.project_id = p.id AND pm.status_id = 10 ) milestone_10 FROM projects p JOIN locations l ON l.id = p.location_id