SQL? "Show me all the Activities that can be performed by User X."

I'm at my wits end. I've searched like crazy, read开发者_JAVA百科 plenty of database textbooks and God knows how many online guides, blogs and forums. Can someone put me out of my misery?

PseudoSchema Diagram (Warning: although pretty, not in a format even remotely approaching ER diagrams! Primary keys are bold, foreign keys are italics.)

Given the above set of tables relating Users to Groups to Privileges, and then a set of Activities that can each require a Privilege to perform, how do you find out this:

Show me all the Activities that can be performed by User X. That is: User X is a member of groups G and F; groups G and F correlate to privileges P, Q, R and S; show me all the activities that require privileges either P, Q, R or S.

How do you do this?

---

 SELECT DISTINCT Activity.id, Activity.name, Activity.description 
    FROM Activity 
    INNER JOIN Privilege ON Activity.requires_privilege = Privilege.id
    INNER JOIN Group ON Privilege.group_id = Group.id
    INNER JOIN UserGroup ON Group.id = UserGroup.group_id
 WHERE UserGroup.user_id = :X

should get what you want. There are other alternatives.

---

select a.*
from user u
inner join usergroup ug on u.id = ug.user_id
inner join privilege p on ug.group_id = p.group_id
inner join activity a on p.id = a.requires_privilege
where u.name = 'X'