Concatenating all matches from a join table into a column

Question

I have two MySQL tables (table_a and table_b) and a join table (table_c).

Table Structures:

• table_a:

__________________
| table_a:       |
|----------------|
| id             |
| result_column  |
------------------

• table_b:

__________________
| table_b:       |
|----------------|
| id             |
| name           |
------------------

• table_c:

__________________
| table_c:       |
|----------------|
| id             |
| table_a_id     |
| table_b_id     |
------------------

My Goal:

I want to find a query that will:

1. Iterate over every table_a record and get the table_a.id value
2. Find any records in table_c which have a matching table_c.table_a_id value
3. For each matching record in table_c get the table_c.table_b_id value
4. Find the record in table_b which has a matching table_b.id value
5. For that matching record in table_b get the table_b.name value
6. In table_a, concatenate each matched name value into the corresponding table_a.result_column

Example: Before the Query:

_______________________  _________________________________  ________________
| table_a:            |  | table_c:                      |  | table_b:     |
|---------------------|  |-------------------------------|  |--------------|
| id  | result_column |  | id  | table_a_id | table_b_id |  | id  | name   |
|-----|---------------|  |-----|------------|------------|  |-----|--------|
|  1  |               |  |  1  |      1     |      3     |  |  1  | Kevin  |
|  2  |               |  |  2  |      1     |      4     |  |  2  | Jesse  |
|  3  |               |  |  3  |      2     |      2     |  |  3  | Karen  |
-----------------------  |  4  |      3     |      1     |  |  4  | Tim    |
                         |  5  |      3     |      5     |  |  5  | Lauren |
                         ---------------------------------  ----------------

After the Query:

_______________________  _________________________________  ________________
| table_a:            |  | table_c:                      |  | table_b:     |
|---------------------|  |-------------------------------|  |--------------|
| id  | result_column |  | id  | table_a_id | table_b_id |  | id  | name   |
|-----|---------------|  |-----|------------|------------|  |-----|--------|
|  1  | Karen, Tim    |  |  1  |      1     |      3     |  |  1  | Kevin  |
|  2  | Jesse         |  |  2  |      1     |      4     |  |  2  | Jesse  |
|  3  | Kevin, Lauren |  |  3  |      2     |      2     |  |  3  | Karen  |
-----------------------  |  4  |      3     |      1     |  |  4  | Tim    |
                         |  5  |      3     |      5     |  |  5  | Lauren |
                         ---------------------------------  ----------------

For absolute clarity, I understand that this is incredibly bad practice within a relational data-table. This is as far from normalization as one can get. I would never design a database like this. I was tasked with creating a custom column with a list of values purely for a business case.

Answer 1

The query you seem to want is:

select c.table_a_id, group_concat(b.name separator ', ') 
from c join
     b
     on c.table_b_id = b.id
group by c.table_a_id;

If you actually want to update a, you can put this into an update statement:

update a join
       (select c.table_a_id, group_concat(b.name separator ', ') as names
        from c join
             b
             on c.table_b_id = b.id
        group by c.table_a_id
       ) cb
       on cb.table_a_id = a.id
    set result_column = cb.names