From: Date: May 30 2007 11:34pm
Subject: SQL help: Selecting issues with assigned users
List-Archive: http://lists.mysql.com/eventum-devel/555
Message-Id: <200705301734.55455.greg.rundlett@oasis-open.org>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit

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