sql query: return a list that will contain the users / groups who will have access to an application

I’m doing an sql query where I want to return a list that will contain the users / groups who will have access to an application. This is the scheme, we will see if a user have permissions, if not, we going to see the group. If both not, we see if there are any permissions on the application, if not, then will add the user to the permissions, if the user or group have permissions we add to.

SELECT 
    dbo.APPLICATIONS_PERMISSION.USERMASTERID,
    dbo.APPLICATIONS_PERMISSION.GROUPID,
    dbo.APPLICATIONS_PERMISSION.VISIBLE, 
    dbo.APPLICATIONS_PERMISSION.APPLICATIONID
FROM dbo.GROUP 
RIGHT OUTER JOIN
dbo.APPLICATIONS_PERMISSION ON
dbo.GROUP.ID = dbo.APPLICATIONS_PERMISSION.GROUPID 
FULL OUTER JOIN
dbo.USER_MASTER ON 
dbo.APPLICATIONS_PERMISSION.USERMASTERID = dbo.USER_MASTER.ID 
AND dbo.GROUP.ID = dbo.USER_MASTER.GROUPID
WHERE (dbo.APPLICATIONS_PERMISSION.USERMASTERID = 7) 
AND (dbo.APPLICATIONS_PERMISSION.APPLICATIONID = 3)

-Edit- Table Schema

User_Master
  ID
  Name
  GroupID

Aplications_Permissions
   ID
   AplicationsID
   UsermasterID
   GroupID

GROUP
   ID
   Name


--Data Exemple --
User_Master
1 ; Filipe ; 1
2 ; Luis ; 1
3 ; Daniel ; 2
4 ; Toino ; 3

Aplications_Permissions
1 ; 1 ; 2 ; null
2 ; 1 ; null ; 1

Group
1 abc
2 def
3 poi


Result:
input: dbo.APPLICATIONS_PERMISSION.USERMASTERID = 2 // dbo.APPLICATIONS_PERMISSION.APPLICATIONID = 1

will return
True
-----------------------------------------
input: dbo.APPLICATIONS_PERMISSION.USERMASTERID = 3 // dbo.APPLICATIONS_PERMISSION.APPLICATIONID = 1

will return
True
-----------------------------------------
input: dbo.APPLICATIONS_PERMISSION.USERMASTERID = 3 // dbo.APPLICATIONS_PERMISSION.APPLICATIONID = 2

will return
True (because nobody have permissions in that application)
-----------------------------------------
input: dbo.APPLICATIONS_PERMISSION.USERMASTERID = 3 // dbo.APPLICATIONS_PERMISSION.APPLICATIONID = 2

will return
False(dont have permissions, and dont belong to that group)

Answer

Took the liberty of aliasing the tables for readability:

SELECT ap.USERMASTERID, ap.GROUPID, ap.VISIBLE, ap.APPLICATIONID
FROM dbo.GROUP g
RIGHT OUTER JOIN dbo.APPLICATIONS_PERMISSION ap ON g.ID = ap.GROUPID 
FULL OUTER JOIN dbo.USER_MASTER um 
     ON ap.USERMASTERID = um.ID AND g.ID = um.GROUPID
WHERE (um.ID = 7) AND (ap.APPLICATIONID = 3)

If I understand correctly, sounds like we want to return a list of users who either have direct permission on the app, or who are members of a group with permission on the app.

Let’s simplify it by doing those two queries separately:

--Direct permission
SELECT ap.USERMASTERID, NULL AS 'GROUPID', ap.VISIBLE, ap.APPLICATIONID
FROM dbo.APPLICATIONS_PERMISSION ap
INNER JOIN dbo.USER_MASTER um ON ap.USERMASTERID = um.ID
WHERE (ap.USERMASTERID = 7) AND (ap.APPLICATIONID = 3)

UNION ALL

--Permission through group membership
SELECT ap.USERMASTERID, ap.GROUPID, ap.VISIBLE, ap.APPLICATIONID
FROM dbo.APPLICATIONS_PERMISSION ap
INNER JOIN dbo.Group g ON ON g.ID = ap.GROUPID 
INNER JOIN dbo.USER_MASTER um ON g.USERMASTERID = um.ID
WHERE (um.ID = 7) AND (ap.APPLICATIONID = 3)

Might have to play with that a little to get exactly what you need, but I would say: do them separately. Might not even need the join to the User_Master table, since it doesn’t look like you’re actually retrieving any fields from it. Just filter from ap.UserMasterID or g.UserMasterID.