Help with returning a comma delimited list

[anthony]

I have a problem I just can't seem to solve.

I have a table with a project ID and another table with the project ID and the usernames of the people assigned to the project. I also have a users table which stores the first and last name of the user based on the username.

I need to query the project table for all projects of a specific supervisor which includes a field that is a comma seperated list of the names of the people the project is assigned to.

Here are the tables:

projects: projID,createdBy,createdOn,projName,projDesc,active
projassignment: projID, userName
users: username,fname,lname,...

Here is the query:

SELECT projects.projID,
projects.createdBy,
projects.createdOn,
projects.projName,
projects.projDesc,
(
SELECT CONCAT(users.fname,' ',users.lname)
FROM users,projassignment
WHERE projassignment.userName = users.userName

) AS assignedTo
FROM projects,admin,users
LEFT JOIN projassignment ON projects.projID = projassignment.projID
WHERE admin.sa = '#SESSION.User.userName#'
AND admin.userName = projects.createdBy
AND projects.createdBy = users.userName
AND projects.active = 1

You can see that I have to join the projectID from the project table to the projassignment which gets me the usernames. Then I have to join the usernames to the users table to get the first and last names. There can be multiple users though which will return multiple rows. of fname lname. In the subquery (which is obviously not corrent) I want it to return a comma seperated list of the the 'fname lname' as a column name assignedTo which I can parse out later and do whatever I want when I display the query data.

I tried something like:
SELECT list = CONCAT(list + users.fname,' ',users.lname,',')
and
SELECT CONCAT(list + users.fname,' ',users.lname,',') AS list

But neither of them do what I need it to do.

Bottom line, I want one row per project on the given criteria with a column assignedTo which has a comma seperated list of the names of the people assigned to the project.

I hope I am being clear...

Any help would be greatly appreciated.