What is this type of query called, and how can I formulate it?

Question

I have following tables:

events(event_id, name, description)

groups(group_id, event_id, title, description)

event_group(event_id, group_id)

users(user_id, name, email)

event_user(event_id, user_id)

group_user(group_id, user_id)

As an example, how can I return the users who belong to a certain group which belongs to a certain event?

Answer 1

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.