GROUP data in one record (many-to-many)

Question

My database structure is the following:

Table RESERVATION

• ID
• responsible_person
• number_of_persons

Table SIZE

• ID
• size

Many-to-many Table RESERVATIONS_HAS_SIZE

• RESERVATION_ID
• SIZE_ID

A person can register for example 3 persons. You have to choose the sizes of these 3 peoples (S, M or L).

My query looks like this:

SELECT * FROM RESERVATION
INNER JOIN RESERVATIONS_HAS_SIZE
ON RESERVATION.ID = RESERVATIONS_HAS_SIZE.RESERVATION_ID
INNER JOIN SIZES
ON RESERVATIONS_HAS_SIZE.SIZE_ID = SIZE.ID

The problem is now I'm getting 3 records back. I only want one record back and for example one field with sizes: S, S, L.

Is this possible? And if so, how can I do this?

Answer 1

You can use GROUP_CONCAT():

SELECT GROUP_CONCAT(s.Size SEPARATOR ', ')
FROM RESERVATION r INNER JOIN
     RESERVATIONS_HAS_SIZE rhs
     ON r.ID = rhs.RESERVATION_ID INNER JOIN
     SIZES s
     ON rhs.SIZE_ID = s.ID;