MySQL - concatenate and joins

Question

have 3 following tables:

users (id, name)
projects (id, name)
user_to_project (user_id, project_id)

Every user can be assigned to more than one project and this is stored in the user_to_project table. I want to get a user name and all the projects he's assigned to in one field separated with commas. I tried something like this:

SELECT 
users.id AS 'ID', 
users.name AS 'Name', 
(SELECT GROUP_CONCAT (projects.name SEPARATOR ', ') 
FROM user_to_project 
INNER JOIN projects ON (projects.id = user_to_project.project_id)
INNER JOIN users ON (users.id = user_to_project.user_id)) AS 'Projects'
FROM users

It gets me all assigned projects in every row which is not that I want. How to fix this?

In order to get one result row per user_id, you'd have to aggregate your records by using GROUP BY user_id. — Thorsten Kettner
– Thorsten Kettner, Commented Jun 8, 2016 at 11:30
That's not the only problem, I don't understand how this even works without a relation condition to the outer query, should throw an error More then one row.. @ThorstenKettner — sagi
– sagi, Commented Jun 8, 2016 at 11:31
@sagi: You are right, this is not the only mistake, but it is what to start with :-) The subquery returns one value only: the conactenation of all projects over all records. — Thorsten Kettner
– Thorsten Kettner, Commented Jun 8, 2016 at 11:34

Gordon Linoff · Accepted Answer · 2016-06-08 11:31:38Z

3

You can do this with a subquery, but you want a correlation clause:

SELECT u.id, u.name, 
       (SELECT GROUP_CONCAT(p.name SEPARATOR ', ') 
        FROM user_to_project tup INNER JOIN
             projects p
             ON p.id = utp.project_id 
        WHERE u.id = utp.user_id
       ) as Projects
FROM users u;

Notes:

Use table aliases. They make a query easier to write and to read.
Don't use single quotes for column aliases. Only use single quotes for string and column names (and your column aliases don't require any escape character).
This is different from a version using INNER JOIN, because this will keep all users, even those with no projects.

answered Jun 8, 2016 at 11:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sagi · Accepted Answer · 2016-06-08 11:44:05Z

0

I didn't see any reason for this correlated query, and you were missing a condition inside to relate it to the outer query. You also needed a group by clause.

This query should give you all the projects for each ID :

SELECT users.id, users.name , 
       GROUP_CONCAT (projects.name SEPARATOR ', ') 
FROM user_to_project 
INNER JOIN projects ON (projects.id = user_to_project.project_id)
INNER JOIN users ON (users.id = user_to_project.user_id) 
GROUP BY users.id,users.name

Note: To make your query work, all you need is to drop the users table from the inner query, and keep the condition.

edited Jun 8, 2016 at 11:44

answered Jun 8, 2016 at 11:28

sagi

40.6k7 gold badges66 silver badges87 bronze badges

3 Comments

sagi Over a year ago

Who is always down voting me for the correct answer?!

Thorsten Kettner Over a year ago

Does that happen more often to you? I, too, was surprised you got a downvote. Makes no sense. (There is a closing parenthesis too many in your query, by the way. And Gordon is right in his answer to point out that the wrong type of quotes is being used on the alias names. But anyway, your answer is good!)

sagi Over a year ago

Yea, I had typos there:S . Yes, not to much, but enough to make me suspicious and angry(second one for today) .. @ThorstenKettner

Collectives™ on Stack Overflow

MySQL - concatenate and joins

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related