CODEWITHSUNDEEP
mysqlsqlconcatenation
Mike
Mike

Reputation: 105

MySQL - concatenate and joins

have 3 following tables:

  1. users (id, name)
  2. projects (id, name)
  3. 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?

Upvotes: 1

Views: 77

Answers (2)

sagi
sagi

Reputation: 40481

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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.

Upvotes: 3

Related Questions