My query doesn't work. What can I do?

Question

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

See GROUP_CONCAT for how to quickly get a denormalized "users" text column - that is "multiple rows to one column". Use a GROUP BY as well. This works OK for denormalizing single values such as names (but does poor at handling associated values). Alternatively, do the processing in the client. — user2864740, Apr 26 '14 at 19:20

score 1 · Answer 1 · answered Apr 26 '14 at 19:16

1

Use an IN clause, e.g.

JOIN users ON users.id IN (SELECT userid FROM ....)

= is for comparing two single values. IN is used to check if a single value is in a set of values.

answered Apr 26 '14 at 19:16

Marc B

356,200
43
426
500

echo_Me · Accepted Answer · 2014-04-26T19:49:22.073

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

edited Apr 26 '14 at 19:49

answered Apr 26 '14 at 19:27

echo_Me

37,078
5
58
78

The left join doesn't work either. I can't see the users at the end of the table – darksoul90 Apr 26 '14 at 19:31
can you explain doesnt work ? some error ? wrong result ? – echo_Me Apr 26 '14 at 19:32
I try the queries in phpmyadmin, there are no errors, the result is the PROJECT table's content and every project appears 4 times. So there are 4 rows of the project one, 4 rows of the project2... – darksoul90 Apr 26 '14 at 19:35
try that updated answer , added group by – echo_Me Apr 26 '14 at 19:37
Great, now there are only 2 rows, but there are no users at the end of the table. I think I should do something with the GROUP_CONCAT – darksoul90 Apr 26 '14 at 19:39
dude if you made some sample data and wished result so maybe we can understand and help you out. – echo_Me Apr 26 '14 at 19:41
you are not selecting users.name . try that updated answer – echo_Me Apr 26 '14 at 19:41
Amazing, but instead of users.name I wrote GROUP_CONCAT( users.name SEPARATOR ',') AS usernames – darksoul90 Apr 26 '14 at 19:45
yes you can do like that to get all users , just select what you want :) – echo_Me Apr 26 '14 at 19:48
1

you welcome :) , you dont need separator here because its automatically will be `,` . if you want to change , to something else then use separator . – echo_Me Apr 26 '14 at 19:50
How can I use some kind of new line character as a separator? Only a
? – darksoul90 Apr 26 '14 at 19:57
1

check this post http://stackoverflow.com/questions/8285035/group-concat-mysql-new-line you can use SEPARATOR '\n' – echo_Me Apr 26 '14 at 20:01

score 1 · Answer 3 · answered Apr 26 '14 at 19:39

I assume you want all the users in a cell of your result table but only one copy of the other fields in their cells. If you really want this in one table you need to parse the results in code. Use a query that has all the fields you want like so:

SELECT project.id, title, description, status, start,end, users.name
FROM project
LEFT JOIN roles On roles.projectId = project.id
LEFT JOIN users ON roles.userId = users.id

Then in your code check for the duplicates and don't display them. Pseudo code example:

loop over all result rows {
   if( new_id <> old_id ){
      if not first row close your previous row </tr>
      display <tr>
      display all the fields
   }else{
       only display the addition of the user name (with a <br> or whatever to separate)
   }
}
close your last row </tr>

This will probably display it the way you want but the data is not aligned for one table of display. Consider a link to show the user list as a popup DIV.

My query doesn't work. What can I do?

3 Answers3