MySQL - getting an associated list of values from one table in a query with a GROUP BY from another table

user_3622569 0
Viewed 46

I have a table with users, a table with assignments, and a table with tasks each associated with an assignment and user. I want to get the list of students that have a task on a given assignment. (I've tried to simplify this DB sample as much as possible for my question.)

Here's an SQL Fiddle: http://sqlfiddle.com/#!9/625a13/1

For reference, here's what the DB structure looks like:

CREATE TABLE users (
  id SERIAL,
  name VARCHAR(32),
  PRIMARY KEY (id)
);

CREATE TABLE assignments (
  id SERIAL,
  title VARCHAR(45),
  assigned_by_user_id INT,
  PRIMARY KEY (id)
);

CREATE TABLE tasks (
  id SERIAL,
  user_id INT,
  assignment_id INT,
  complete INT,
  PRIMARY KEY (id)
);

INSERT INTO users VALUES (1, 'Student Joe'), (2, 'Student Fred'), (3, 'Teacher Bob');
INSERT INTO assignments VALUES (1, 'Math Homework', 3), (2, 'History Homework', 3), (3, 'Science Homework', 3);
INSERT INTO tasks VALUES (1,1,1,1), (2,1,2,1), (3,1,3,0), (4,2,1,1), (5,2,2,0), (6,2,3,0);

And here's a sample of a query from this data:

SELECT a.id, a.title, u.name AS teacher_name, '' AS students_included,
(COUNT(CASE WHEN t.complete = 1 THEN t.id ELSE NULL END)/COUNT(t.id)*100) AS percent_complete
FROM assignments AS a
JOIN users AS u ON a.assigned_by_user_id = u.id 
LEFT JOIN tasks AS t ON a.id = t.assignment_id 
GROUP BY a.id

Here's a screenshot of what I have: DB sample

Here's my target result: DB sample - target

How can I have the query return the student names that are associated to those assignments? In some cases, there might not be any tasks (no students), in some cases one student, in some cases multiple students. I'd like to somehow get an array / CSV list of student names in the associated column.

(I know I could do separate queries for each line - for instance in PHP as I loop through the results - and get this result, but that would be slow, cumbersome, and resource intensive - I'd like to get the student name values with a single MySQL query if at all possible - I just can't think of how to do it.)

mysql sql
3 Answers

You may use GROUP_CONCAT (similar to LISTAGG in Oracle), and would require one more join with USERS table to pull students' names

SELECT a.id, a.title, u.name AS teacher_name, GROUP_CONCAT(stu.name) AS students_included,
(COUNT(CASE WHEN t.complete = 1 THEN t.id ELSE NULL END)/COUNT(t.id)*100) AS percent_complete
FROM assignments AS a
JOIN users AS u ON a.assigned_by_user_id = u.id 
LEFT JOIN tasks AS t ON a.id = t.assignment_id
LEFT JOIN users AS stu ON stu.id = t.user_id 
GROUP BY a.id, a.title, u.name

Do not forget adding columns to GROUP BY clause.

user_70607750

You need to use a GROUP_CONCAT and also join another user table

SELECT a.id, a.title, u.name AS teacher_name, GROUP_CONCAT(us.name)  AS students_included,
(COUNT(CASE WHEN t.complete = 1 THEN t.id ELSE NULL END)/COUNT(t.id)*100) AS percent_complete
FROM assignments AS a
JOIN users AS u ON a.assigned_by_user_id = u.id
LEFT JOIN tasks AS t ON a.id = t.assignment_id 
JOIN users AS us ON t.user_id = us.id 
GROUP BY a.id
user_51935360

I think I may have figured this out...

SELECT a.id, a.title, u.name AS teacher_name, GROUP_CONCAT(u_s.name SEPARATOR ', ') AS students_included,
(COUNT(CASE WHEN t.complete = 1 THEN t.id ELSE NULL END)/COUNT(t.id)*100) AS percent_complete
FROM assignments AS a
LEFT JOIN tasks AS t ON a.id = t.assignment_id 
JOIN users AS u ON a.assigned_by_user_id = u.id 
LEFT JOIN users AS u_s ON t.user_id = u_s.id 
GROUP BY a.id

Still playing with it... but someone may have a better idea...

user_36225690
Related