0

I am looking for some feedback regarding the best way to handle the following (simplified for this example) situation.

I have 3 tables: tasks, assignees, and users

---tasks----
id, task, completed

---assignees----
user_id, task_id

---users---
id, name

I need to select tasks with concatenated user names of assignees, like so

tasks.task  |   assigned_names.names   
---------------------------------------
"My task"   |   "Joe Schmoe, Bob Mcbart, Sally McGuire"

Here is what I have currently

SELECT tasks.task, assigned_names.names

FROM tasks

LEFT JOIN (
   SELECT 
      GROUP_CONCAT( users.name SEPARATOR ',' ) AS names,
      assignees.task_id
      FROM assignees
      LEFT JOIN users ON users.id = assignees.user_id
      GROUP BY assignees.task_id
   
) assigned_names ON assigned_names.task_id = tasks.id

WHERE tasks.completed IS NULL

My main concern is that the assignees table has hundreds of thousands of records, and doing a group on the whole table every time seems like not a good idea.

Is there a better strategy?

Also, I would be happy to rephrase my question title if someone could suggest better language.

Thanks!

3
  • Why use a subselect and not join the two tables directly?
    – mustaccio
    Aug 14, 2020 at 19:09
  • Would that be more efficient? I was doing that at first, but I got the following error Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tasks.task' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by. I don't want to group by tasks.task as well, as it is a text column
    – Ben
    Aug 14, 2020 at 19:17
  • You won't know until you try (and look at the query plan as well).
    – mustaccio
    Aug 14, 2020 at 19:19

1 Answer 1

0

Simplicity is beautify. Multiple JOINs are allowed. There is no need to the JOINs to be LEFT JOINS as you want data that maches always.

SELECT tasks.task, GROUP_CONCAT( users.name SEPARATOR ',' ) AS names,
FROM assignees
JOIN tasks ON assignees.task_id = tasks.id
JOIN users ON assignees.user_id = users.id
WHERE tasks.completed IS NULL
GROUP BY assignees.task_id
1
  • If you get a a group by non-aggregate field in result error, using tasks.id in the GROUP BY is equivalent.
    – danblack
    Aug 14, 2020 at 23:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.