Mysql – Combine Multiple Rows Results From Left Join in a unique cell

I updated class_teachers table to class_teachers_section to include section. The idea is a class can include different sections with different teachers. For example a grade 1 PE class can include all sections; the teacher for boys will be different from that for girls.

select class.name as classname, 
   class.hours as hours, 
   teachers.first_name as teacherFirstName, 
   teachers.last_name as teacherLastName,
   section.name as sectionName
 from class_teachers_section cls 
 left join class on class.id = cls.class_ref  
 left join teachers on teachers.id = cls.teachers_ref
 left join section on section.id = cls.section_ref
 where section.name = 'grade 1'

Following what's already written at How to use GROUP_CONCAT in a CONCAT in mysql...

Let's assume you have a schema and sample data equivalent to:

CREATE TABLE properties
(
    id integer PRIMARY KEY,
    property varchar(255) NOT NULL
) ;

CREATE TABLE recommended_providers
(
   id integer PRIMARY KEY,
   recommended_provider_name varchar(255) NOT NULL
) ;

CREATE TABLE properties_recommended_providers
(
   prop_id INTEGER NOT NULL REFERENCES properties(id),
   recommended_provider_id INTEGER NOT NULL REFERENCES providers(id),
   PRIMARY KEY (prop_id, recommended_provider_id)
) ;

INSERT INTO properties
VALUES (1, 'property 1'), (2, 'property 2') ;

INSERT INTO recommended_providers
VALUES (1, 'provider 1'), (2, 'provider 2'), (3, 'provider 3');

INSERT INTO properties_recommended_providers
VALUES (1, 1), (1, 2), (2, 3) ;

You can either have:

SELECT
  p.id AS property_id, property, 
  GROUP_CONCAT(recommended_provider_name SEPARATOR ', ') AS provider_names
FROM
  properties p
  LEFT JOIN properties_recommended_providers pr ON pr.prop_id = p.id
  LEFT JOIN recommended_providers rp ON rp.id = pr.recommended_provider_id
GROUP BY
  property_id
ORDER BY
  property_id ;

(Check it at SQL Fiddle)

or

SELECT
  p.id AS property_id, property, 
  (SELECT
      GROUP_CONCAT(recommended_provider_name SEPARATOR ', ')  
   FROM
       properties_recommended_providers pr
       LEFT JOIN recommended_providers rp ON rp.id =
                                         pr.recommended_provider_id
   WHERE
       pr.prop_id = p.id
   ORDER BY
       /* You can easily order by in this case */
       recommended_provider_name
   ) AS provider_names
FROM
  properties p
ORDER BY
  property_id ;

You can check the second one at SQL Fiddle