CODEWITHSUNDEEP
mysqlsqlmany-to-manyinner-join
nielsv
nielsv

Reputation: 6800

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

My database structure is the following:

Table RESERVATION

Table SIZE

Many-to-many Table RESERVATIONS_HAS_SIZE

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?

Upvotes: 2

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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;

Upvotes: 4

Related Questions