CODEWITHSUNDEEP
mysqlsql
Sife
Sife

Reputation: 3

MySQL JOIN multiple results

I am trying to select all of the roles a specific user has access to within a specific server. This is for a system that allows a user to manage one or more services. The amount of access a user has is assigned by whoever the service belongs to. Roles are grouped and that group is then what gets assigned to a user. A user may have more than one group.

This is the query that I made and expected to work, but it doesn't. I am guessing it doesn't work because the serverPermissions table can return more than 1 groupId based on what a user is assigned.

SELECT serverGroupRoles.roleId FROM `serverGroupRoles`, `serverPermissions`, `servers` 
    WHERE servers.identifier='someUniqueString' AND 
        serverPermissions.serverId=servers.id AND 
        serverPermissions.userId=1 AND 
        serverGroupRoles.groupId=serverPermissions.groupId

Here's a visual look of the tables, 'servers' table has other data, but it's unrelated.

servers table, identifier is a unique key:

id  |  identifier  |  ...
--------------------------
 1  |  someString  |  ...
 2  | someString02 |  ...

serverPermissions table:

serverId  |  groupId  |  userId
--------------------------------
    1     |     1     |     1
    1     |     2     |     1
    1     |     2     |     2
    2     |     3     |     1
    3     |     4     |     1

serverGroupRoles table:

groupId  |  roleId
------------------
   1     |    1
   1     |    2
   1     |    3
   2     |    1
   2     |    3
   3     |    4
   4     |    2

The roleId's are mapped in the application to a certain action.

This is what I am trying to accomplish, but with 1 query: If you did something like,

SELECT id FROM `servers` WHERE identifier = 'someString'

Returns

id
--
 1

Then took the id that was returned from that,

SELECT groupId FROM `serverPermissions` WHERE serverId = 1 AND userId = 1

Then it would return

groupId
-------
   1
   2

Then with each groupId, 

SELECT roleId FROM `serverGroupRoles` WHERE groupId = #

And the end result,

roleId
------
   1
   2
   3

Is there a good way to do this with 1 query?

Edit, query that accomplishes the task:

SELECT DISTINCT sgr.roleID
FROM serverPermissions sp
    INNER JOIN servers s ON s.id = sp.serverID
    INNER JOIN serverGroupRoles sgr ON sgr.groupID = sp.groupID
WHERE sp.userID = 1
    AND s.identifier = 'someString'

Upvotes: 0

Views: 1047

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

Is this what you want?

SELECT roleId
FROM `serverGroupRoles`
WHERE groupId in (SELECT groupId
                  FROM `serverPermissions`
                  WHERE serverId = 1 AND userId = 1 
                 )

Perhaps you actually want "SELECT distinct roleID" to eliminate duplicates.

You can extend this for servers, but I would do it as a set of joins:

SELECT distinct roleId
FROM `serverGroupRoles` sgr join
     `serverPermissions` sp
      on sgr.groupId = sp.groupId join
     `server` s
      on sp.serverid = s.id
WHERE s.identifier = 'someString' AND sgr.userId = 1

Upvotes: 0

Nicholas
Nicholas

Reputation: 2060

It's still a bit early here, but would this do what you want:

SELECT  DISTINCT sgr.roleID
FROM    serverPermissions sp
        INNER JOIN serverGroupRoles sgr ON sgr.groupID  = sp.groupID
WHERE   sp.serverID = 1
        AND sp.userID   = 1

I could be off the mark here as I'm not sure where the servers table comes into this. If you're looking for data from that table you can join it in too:

SELECT  DISTINCT sgr.roleID, s.fieldName
FROM    serverPermissions sp
        INNER JOIN servers s            ON s.id = sp.serverID
        INNER JOIN serverGroupRoles sgr ON sgr.groupID  = sp.groupID
WHERE   sp.serverID = 1
        AND sp.userID   = 1

Upvotes: 1

Related Questions