MySQL join and concat rows without repeating entries

Question

I have a question about how to merge multiple row output into one row without having the same entry multiple times. The basic setup is 4 tables:

room
appointments
users
actions

And 2 intermediate tables:

actions_appointments
users_appointments

I post the exact structure of the tables at the end of my post.

Multiple appointments can be made for one entry in the room table (n:1), but one or more users can join appointments (n:n) and one or more actions can be performed (n:n). The problem is that I don't know how to output a single appointment with each user and action only being displayed ONCE per appointment.

With the example tables at the bottom of this post I basically want this to be my output:

|----------------|-----------|---------------------|-------------|------------------------|
| appointment_id | room_name |      datetime       |   actions   |     userfullnames      |
|----------------|-----------|---------------------|-------------|------------------------|
|        1       | Studio    | 2016-09-01 15:30:00 | work, sleep | John Doe, Martin Smith |
|        2       | Office    | 2017-04-02 13:00:00 | sleep       | John Doe               |
|----------------|-----------|---------------------|-------------|------------------------|

But with the queue I came up with I get this:

|----------------|-----------|---------------------|-------------|------------------------|
| appointment_id | room_name |      datetime       |   actions   |     userfullnames      |
|----------------|-----------|---------------------|-------------|------------------------|
|        1       | Studio    | 2016-09-01 15:30:00 | work, sleep,| John Doe, Martin Smith,|
|                |           |                     | work, sleep | John Doe, Martin Smith |
|        2       | Office    | 2017-04-02 13:00:00 | sleep       | John Doe               |
|----------------|-----------|---------------------|-------------|------------------------|

I mean I kinda get that I screwed up my joins but I'm totally stuck at the moment. Any hints? I feel like the solution is simple but I'm totally blind at the moment.

My queue:

SELECT 
    appointments.id AS 'appointment_id', 
    room.name AS 'room_name', 
    appointments.datetime, 
    GROUP_CONCAT(actions.name SEPARATOR ', ') AS 'actions',
    GROUP_CONCAT(users.givenname, ' ', users.surname SEPARATOR ', ') AS 'userfullnames'

FROM appointments
INNER JOIN actions_appointments
        ON appointments.id = actions_appointments.appointments_id
INNER JOIN actions
        ON actions_appointments.actions_id = actions.id
INNER JOIN users_appointments
    ON users_appointments.appointments_id = appointments.id
INNER JOIN users
    ON users_appointments.users_id = users.id
INNER JOIN room
    ON appointments.room_id = room.id
GROUP BY
    appointments.id;

Table structure:

The basic tables:

|-------------------|
|   room            |
|-------------------|
|   id   | name     |
|--------|----------|
|    1   | Office   |
|    2   | Studio   |
|-------------------|


|----------------------------------------|
|   appointments                         |
|--------|---------|---------------------|
|   id   | room_id |      datetime       |
|--------|---------|---------------------|
|    1   |    2    | 2016-09-01 15:30:00 |
|    2   |    1    | 2017-04-02 13:00:00 |
|--------|---------|---------------------|


|-----------------------------------------|
|   users                                 |
|-----------------------------------------|
|   id   | username | givenname | surname |
|--------|----------|-----------|---------|
|    1   | j.doe    | John      | Doe     |
|    2   | m.smith  | Martin    | Smith   |
|--------|----------|-----------|---------|


|--------------------|
|   actions          |
|--------------------|
|   id   | name      |
|--------|-----------|
|    1   | work      |
|    2   | sleep     |
|--------------------|

The intermediate tables:

|------------------------------|
|   actions_appointments       |
|------------------------------|
| actions_id | appointments_id |
|------------|-----------------|
|        1   |             1   |
|        2   |             1   |
|        2   |             2   |
|------------|-----------------|


|----------------------------|
|   users_appointments       |
|----------------------------|
| users_id | appointments_id |
|----------|-----------------|
|      1   |        1        |
|      2   |        1        |
|      1   |        2        |
|----------|-----------------|

Edit: The correct queue with DISTINCT Thanks to Juan.Queiroz and Mike!

SELECT 
    appointments.id AS 'appointment_id', 
    room.name AS 'room_name', 
    appointments.datetime, 
    GROUP_CONCAT(DISTINCT actions.name SEPARATOR ', ') AS 'actions',
    GROUP_CONCAT(DISTINCT users.givenname, ' ', users.surname SEPARATOR ', ') AS 'userfullnames'

FROM appointments
INNER JOIN actions_appointments
        ON appointments.id = actions_appointments.appointments_id
INNER JOIN actions
        ON actions_appointments.actions_id = actions.id
INNER JOIN users_appointments
    ON users_appointments.appointments_id = appointments.id
INNER JOIN users
    ON users_appointments.users_id = users.id
INNER JOIN room
    ON appointments.room_id = room.id
GROUP BY
    appointments.id;

Invalid GROUP BY, wont run on newer MySQL versions. (The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function.) — jarlh, Aug 25 '16 at 13:46
@Mike Oh god I knew it, I just forgot about DISTINCT. You're totally right, thanks alot, that solved it. It's too warm in here. — Broco, Aug 25 '16 at 13:58

score 0 · Answer 1 · answered Aug 25 '16 at 13:51

0

GROUP BY
appointments.id, 
room.name, 
appointments.datetime

answered Aug 25 '16 at 13:51

verhie

1,298
1
7
7

MySQL join and concat rows without repeating entries

1 Answers1