Mysql – Joining Three Tables by using IDs as Foreign Keys

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.

For the Users, Groups and userGroupLink tables you have the relationships correct.

I would suggest you change groups.user_id to owner_user_id just to make it a little bit clearer, but that's a minor point of preference. Since userGroupLink has no attributes of its own nor is it the parent in any relationships you could remove the ID column from this table without any loss of meaning or utility. Indeed you will get a small performance boost by storing more rows per page.

To find all the groups of which a user is a member you would join the tables together:

select
    u.id
    ,u.username
    ,g.group_name
from mydb.users as u
inner join  mydb.userGroupLink as l
    on l.user_id = u.id
inner join mydb.groups as g
    on g.id = l.group_id
where u.id = <the user id you are looking for>

If you are looking for the groups which a user owns that would follow the other relationship.