Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
I know you posted this answer a while ago, but I'm trying to learn SQL and wrap my head around joins in my spare time — they're kind of doing my head in. When you select * from users as you did, and then do a join with group_user and then with event_group are you now selecting * from all three of these tables or still just users? –  bernk Jul 28 '12 at 10:47
    
@bernk In my code only users, because I specify U.*. If you only specified * with no table alias before it, then it would return all fields from all tables. –  JNK Jul 28 '12 at 11:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.