CODEWITHSUNDEEP
mysql
annoyingmouse
annoyingmouse

Reputation: 5689

MySQL junction table concatenation

I have an requirement to return all educators associated with a session as a concatenated list, but to do it within a join (don't ask - I know there are multiple ways of doing it but I'm working with a library that won't accept those methods ;-))

I have a sessions table with session_id and session_name.

| session_id | session_name |
+------------+--------------+
| 1          | Swimming     |
| 2          | Chess        |

I have a session_educators table which is basically a junction table session_id and contact_id.

| session_id | contact_id |
+------------+------------+
| 1          | 1          |
| 1          | 2          |
| 2          | 3          |
| 2          | 4          |

I have a contacts table with contact_id and full_name

| contact_id | full_name    |
+------------+--------------+
| 1          | Fred Bloggs  |
| 2          | Mary Bloggs  |
| 3          | Mark Smith   |
| 4          | Shelly Smith |

So far this seems to be the closest I've come:

SELECT
    sessions.session_id,
    sessions.name,
    educators.names
FROM
    `sessions`
LEFT JOIN
    (
        SELECT
            GROUP_CONCAT(contacts.full_name SEPARATOR ', ') as names
        FROM
            `contacts`
        WHERE
            contact_id 
        IN 
            (
                SELECT
                    contact_id
                FROM
                    session_educator
                WHERE
                    session_educator.session_id = sessions.session_id
            )
    ) `educators`
USING
    (`session_id`)

But I'm really fumbling in the dark trying to figure it out, can anyone help?

What I'm wanting, as you can probably tell from the query, is a result like this:

| session_id | session_name | educators                |
+------------+--------------+--------------------------+
| 1          | Swimming     | Fred Bloggs, Mary Bloggs |
| 2          | Chess        | Mark Smith, Shelly Smith |

Any help greatly appreciated - even if it's just to say it can't be done!

Upvotes: 3

Views: 77

Answers (2)

Tersosauros
Tersosauros

Reputation: 923

I do believe you're making it all a little more complex than needed (unless I've missed something in your requirements? I know how much of a pain libraries can be...)

This worked for me:

SELECT
    session_id,
    session_name,
    (
        SELECT
            GROUP_CONCAT(full_name SEPARATOR ', ') as names
        FROM
            contacts c,
            session_educators se
        WHERE
            c.contact_id = se.contact_id
        AND
            se.session_id = s.session_id
    )
FROM
    sessions s
;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You shouldn't need subqueries for this. It is a few joins and an aggregation:

select s.session_id, s.session_name,
       group_concat(e.name separator ', ') as educators
from sessions s left join
     session_educators se
     on se.session_id = s.session_id left join
     educators e
     on e.educator_id = se.educator_id
group by s.session_id;

Upvotes: 2

Related Questions