Re: SQL help: Selecting issues with assigned users

[Bryan Alsdorf]

You should use the GROUP_CONCAT function, put 
GROUP_CONCAT(usr_full_name) in the field list and then GROUP BY iss_id 
at the end of the query. This should pull what you need.

Best Regards,
/bryan

Greg Rundlett wrote:
> I am trying to create a SQL statement that will output a list of issues, 
> including the (list of) users who are assigned to each issue.  This data is 
> found in the three tables: issues, issue_usr, and user
> 
> What I want: a condensed information format:
> task 27,  "summary",  assigned: joe, harry, john
> 
> What I'm getting:
> task 27, "summary" joe
> task 27, "summary" harry
> task 27, "summary" john
> 
> The following query works, but is there a way to summarize or 'implode' the 
> users so that I can just show the task once in the result.  I know I can do 
> this in PHP code, but I was trying to do this all with SQL, perhaps with 
> variables or temp tables?
> 
> SELECT iss_id, project_priority.pri_title, iss_summary, iss_description, 
> user.usr_full_name
> FROM `eventum`.`issue`
> LEFT JOIN `eventum`.`status` ON issue.iss_sta_id = status.sta_id
> LEFT JOIN project_priority ON issue.iss_pri_id = project_priority.pri_id
> LEFT JOIN issue_user ON issue.iss_id = issue_user.isu_iss_id
> LEFT JOIN user ON issue_user.isu_usr_id = user.usr_id
> WHERE status.sta_is_closed =0
> AND (
> `iss_summary` LIKE CONVERT( _utf8 '%%drupal%%'
> USING latin1 )
> COLLATE latin1_swedish_ci
> OR `iss_description` LIKE CONVERT( _utf8 '%%drupal%%'
> USING latin1 )
> COLLATE latin1_swedish_ci
> )
> ORDER BY pri_title ASC
> 

-- 
Bryan Alsdorf, Manager of Support Systems
MySQL AB, www.mysql.com

Discover new MySQL Monitoring & Advisory features at:
http://www.mysql.com/products/enterprise/whats_new.html