MySQL- complex data query in a single statement

Consider the following structure :

alt text http://aeon-dev.org/pap/pap_db.png

Ignore the table user_token.

Now, imagine that you need to get all the roles开发者_JAVA技巧 related to an user, wich may be through it's related groups or directly related to him. In case the same role appears related to a group and the user directly, the role related to the user will prevail over the role given by the group.

Is there any chance this could be done in a single query?

Cheers!

Use:

   SELECT DISTINCT r.name AS role_name
     FROM USER u
LEFT JOIN USER_HAS_ROLE uhr ON uhr.user_id = u.id 
LEFT JOIN USER_HAS_GROUP uhg ON uhg.user_id = u.id 
LEFT JOIN GROUP_HAS_ROLE ghr ON ghr.group_id = uhg.group_id 
LEFT JOIN ROLE r ON r.id = uhr.role_id
                 OR r.id = ghr.role_id
    WHERE u.username = ?

try this:

  Select role_id 
  From user_has_role
  Where userId = @UserId
  Union
  Select role_id 
  From user_has_group g
     Join Group_has_Role gr
        On gr.GroupId = g.GroupId 
  Where userId = @UserId

This query will get every role a single user has either directly or given by a group

SELECT * FROM ROLE WHERE ID IN (
    SELECT ROLE_ID
    FROM USER_HAS_ROLE 
    WHERE USER_ID = 1
    UNION
    SELECT ROLE_ID
    FROM USER_HAS_GROUP UG
    INNER JOIN GROUP_HAS_ROLE GR ON UG.GROUP_ID = GR.GROUP_ID
    WHERE USER_ID = 1
)

You could find all distinct roles for user 1 like:

select  distinct role_id
from    (
        select  uhr.user_id
        ,       uhr.role_id
        from    user_has_role uhr
        union all
        select  uhg.user_id
        ,       ghr.role_id
        from    user_has_group uhg
        join    group_has_role ghr
        on      ghr.group_id = uhg.group_id
        where   not exists
                (
                select  *
                from    user_has_role uhr
                where   uhr.user_id = uhg.user_id
                        and uhr.role_id = ghr.role_id
                )
        ) user2role    
where   user_id = 1

Not sure how a role related to a user should "prevail", but you can assign priorities to them in the union.