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
