Databases
StackOverflow2013
Postsdbo
POHelp with sql query
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHelp with sql query
    text
    copied!<p>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.</p> <pre><code>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) </code></pre> <p>-Edit- Table Schema</p> <pre><code>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) </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload