MySQL - Split field and group concat

I need to join two MySQL tables - events & people, but I see problem when I have multiple people leading the same event.

PEOPLE
=================
ID|Name  |Surname|....
--------------------
 1|John  |Lennon
 2|Paul  |McCartney 
 3|George|Harisson
 4|Ringo |Starr

Is there any possible way to get concat of 1 & 2, if the event table is something like

EVENTS
=================
event |leader|....
-----------------
Picnic|1+2

I do want some events to have only one leader, but also some with more than 2.

Is there any way to do something like this?

Thank you for your help.

Solution

The proper way to solve your problem is to have a events table:

eventid | event
---------------
1       | Picnic

and a events_leaders table:

eventid | leader
----------------
1       | 1
1       | 2

then you can just use a JOIN and a GROUP_CONCAT aggregate function:

SELECT
  events.event,
  GROUP_CONCAT(CONCAT(people.surname, ' ', people.name))
FROM
  events INNER JOIN events_leaders
  ON events.eventid = events_leaders.eventid
  INNER JOIN people
  ON events_leaders.leader = people.id
GROUP BY
  events.eventid,
  events.event

however, you can use FIND_IN_SET function and obtain the same result, but the query will be slower as it works on strings and cannot make use of an index:

SELECT
  events.event,
  GROUP_CONCAT(CONCAT(people.surname, ' ', people.name))
FROM
  events INNER JOIN people
  ON FIND_IN_SET(people.id, REPLACE(events.leaders, '+', ','))>0
GROUP BY
  events.event