MYSQL JOIN 2 tables and return comma separated field

Question

I have two tables:

appointments_recurring_forever

id | text
 1 | some text here 
 2 | other text
 3 | third appt text

recurring_appointments_exclude_dates

id | appt_id | date
 1 | 1       | 2019-01-01
 1 | 1       | 2019-05-21
 2 | 2       | 2020-11-05

I want to join these two tables based on the appointment id, but my expected result needs to look like this:

appt_id | text           | excluded_dates
 1      | some text here | 2019-01-01, 2019-05-21
 2      | other text     | 2020-11-05
 3      | third text     | null

I know how to create joins, but how to does one create a select that joins based on the appt_id while creating a comma separated concatenated row of the excluded dates? This is what I can do so far...

SELECT appts.id AS appt_id, appts.text 
FROM appointments_recurring_forever appts
LEFT JOIN recurring_appointments_exclude_dates ex_date ON ex_date.appt_id = 
appts.id

Thank you for any help.

score 0 · Answer 1 · answered Jun 12 '19 at 18:04

You could use group_concat and group by

SELECT appts.id AS appt_id, appts.text, group_concat(date ) excluded_dates
FROM appointments_recurring_forever appts
LEFT JOIN recurring_appointments_exclude_dates ex_date ON ex_date.appt_id = appts.id
group by  appts.id AS appt_id, appts.text

MYSQL JOIN 2 tables and return comma separated field

1 Answers1