I am using mysql and have three tables one for events one for officials and a mapping table between. How would I query so that the following takes place:

  • One single row per event
  • Concatenate the multiple values for officials into the corresponding single event row.

Tables are structures as such:

**Official** 
-------------
id    name
-------------
1     ali
2     ahmed
3     john
4     king
6     kyle

**Event**
-------------
id    name
-------------
1     event 1
2     event 2
3     event 3

**Mapping**
-------------
id   oid   eid
-------------
1     1     1
2     3     1
3     6     2
4     1     3
5     4     3
6     5     3
7     6     3
1

There are 1 best solutions below

0
On BEST ANSWER

You can make use of INNER JOIN and GROUP_CONCAT, see example below:-

SELECT 
E.id, E.name, GROUP_CONCAT(O.name) OfficialName
FROM Mapping M 
INNER JOIN Event E
    ON M.eid = E.id
INNER JOIN Official O
    ON M.oid = O.id
GROUP BY E.id