我的查询不起作用。 我能做什么？ [重复]

Question

This question already has an answer here:

• Can I concatenate multiple MySQL rows into one field? 11 answers

I would like to display a <table> with these <th>-s:
project title
description
status
start date
end date
users

The problem is, that my subquery returns more than 1 row. Usually 4 people belong to a project, an owner, a manager, a developer and a tester. The owner can create new projects, so he can see the project table I would like to create, but I can't put the users at the end of the table, because more than one belongs to every row.

This is the query:

SELECT project.id, title, description, status, start,end
        FROM project 
        LEFT JOIN users ON (SELECT userid FROM roles,project WHERE projectid=project.id)=users.id

My tables are:

PROJECT
id
title
description
status
start
end

USERS
id
name
pass
email

ROLES
id
projectid
userid
rolename

</div>

Answer 1

Try join all tables instead of subquery like this :

    SELECT project.id, title, description, status, start,`end` ,GROUP_CONCAT( users.name) AS usernames
    FROM project 
    LEFT JOIN roles On roles.projectid = project.id 
    LEFT JOIN users ON roles.userid =users.id
    GROUP BY project.id