How to use a relational database to store all users who belong to a group, and all groups which a user belongs to

The traditional way to model this is using a pattern called Role-Based Security.

The idea is not just to have groups of users, but also groups of permissions. Here is how the pattern looks:

Note that you want to avoid reserved words for table names, so don't name your tables exactly as shown in the diagram.

The way it works is that your groups or Roles have not only a list of users assigned to them but also a list of permissions assigned to them. This allows you to table-drive both who can do what, but also what it is they can do, if you follow me.

I'm not sure if there's a name for this, but you really just want to traverse your relationships with JOINs:

SELECT DISTINCT 
    U.*
FROM
    Users U
INNER JOIN
    Group_user GU
        ON GU.user_id=u.User_id
INNER JOIN
    Event_Group eg
        ON eg.group_id = gu.group_id
WHERE eg.event_id = 1234

I added a DISTINCT in case a user was in more than one group that was in the event.

You can also add a JOIN to the events table if you need another filter (like events taking place on a certain date or whatever).

This is SQL Server syntax but I think it should be basically the same for MySQL, JOIN syntax may vary.