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

Question

I'm at my wits end. I've searched like crazy, read 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?

Answer 1

 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.

Answer 2

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'