I know that they asked more than once, but I can’t find the answer, guys.

I have three tables

students (student_id, first_name, last_name); courses (course_id, name, description); students_courses (student_id, course_id);

Task

Write SQL query that finds students.

I wrote a request

 SELECT s.first_name, s.last_name, c.name FROM courses c INNER JOIN students_courses sc ON sc.course_id = c.course_id INNER JOIN students s ON s.student_id = sc.student_id ORDER BY c.name;

Which prints a table like this

  name | first_name | last_name ------+------------+-------------- Java | Name | SurName Java | Name | SurName Java | Name | SurName Java | Name | SurName JS | Name | SurName JS | Name | SurName ...

And I need this conclusion:

  course_name | full_name ------+------------+-------------- Java | Name Surname1, Name Surname2, Name Surname3... JS | Name Surname1, Name Surname2, Name Surname3...

Ie output via a comma in the part where the names. And also the output of the name of the course in the form of DISTINCT.

thank

    1 answer 1

    Use the concat function.

     SELECT concat(s.first_name,', ', s.last_name) "full_name", c.name FROM courses c INNER JOIN students_courses sc ON sc.course_id = c.course_id INNER JOIN students s ON s.student_id = sc.student_id ORDER BY c.name;
    • Thank you, you helped me a lot. I ended up with SELECT c.name AS course_name, string_agg(concat(s.first_name,' ', s.last_name), ', ') "full_name" FROM courses c INNER JOIN students_courses sc ON sc.course_id = c.course_id INNER JOIN students s ON s.student_id = sc.student_id GROUP BY c.name; - Eugene