Considere el siguiente SQLFiddle .
DROP TABLE IF EXISTS appointments; CREATE TABLE appointments ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL ); DROP TABLE IF EXISTS services; CREATE TABLE services ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL ); DROP TABLE IF EXISTS join_services_appointment; CREATE TABLE join_services_appointment ( service_id INT(6) UNSIGNED, appointment_id INT(6) UNSIGNED, FOREIGN KEY(service_id) references services(id), FOREIGN KEY(appointment_id) references appointments(id) ); INSERT INTO appointments (name) VALUES ('appointment1'); INSERT INTO appointments (name) VALUES ('appointment2'); INSERT INTO appointments (name) VALUES ('appointment3'); INSERT INTO services (name) VALUES ('services1'); INSERT INTO services (name) VALUES ('services2'); INSERT INTO services (name) VALUES ('services3'); INSERT INTO services (name) VALUES ('services4'); INSERT INTO services (name) VALUES ('services5'); INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (1, 1); INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (2, 1); INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (2, 2); INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (3, 2); INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (3, 3); INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (4, 3);¿Qué consulta usaría para obtener los siguientes resultados?
appointment_name services appointment1 services1,services2 appointment2 services2,services3 appointment3 services3,services4Como se menciona en los comentarios, puede usar GROUP_CONCAT() para lograr el resultado deseado.
SELECT a.name AS appointment_name, GROUP_CONCAT(DISTINCT s.name ORDER BY s.name SEPARATOR ', ') AS services FROM appointments a INNER JOIN join_services_appointment sa ON a.id = sa.appointment_id INNER JOIN services s ON s.id = sa.service_id GROUP BY a.id;