0

I have system that has different levels for users depends on what is assigned upon their account was created. So there is four different levels 1,2,3 and 4. First level have full access and then each level below narrows down to data access. So once user logged in I should get records from my Permissions table. One account can have more than one record in permissions table. Here is example:

AccountID   UserName AccessLevel  AccessType State   City  Building
FB3064A7    jfakey        S            F       05     NULL   NULL
FB3064A7    jfakey        S            F       07     NULL   NULL

You can see there is AccessLevel column that can have Region R, State S, City C or Building B. If user has assigned Region access there should be only one record. If there is any other access level (S,C or B) then user can have multiple records. Example above has State level. There is two different states assigned to that account and my query will return two rows. I'm wondering should I split this in two separate queries or stick with once query and merge the rows? I'm wondering what would be more efficient since this is login page there is a lot of hits. Here is example of my query:

SELECT A.AccountID, A.UserName,
    P.AccessLevel, P.AccessType, P.State, P.City, P.Building
FROM Accounts AS A WITH (NOLOCK)
    INNER JOIN Permissions AS P WITH (NOLOCK)
        ON A.AccountID = P.AccountID 
WHERE UserName = 'jfakey'

Query above is what I have now and that returns two rows. If anyone have suggestion on what is the best approach please let me know.

espresso_coffee
  • 5,980
  • 11
  • 83
  • 193
  • If all you need is to return one row for your given SQL statement, include 'TOP 1' in your SELECT statement. Since you have included all columns in your select statement, it looks like you will be using those values to create a login cookie/session. In that case, you need to qualify your WHERE clause with exact level of access you are trying to check for e.g. city or building. – Prashant Agarwal May 22 '18 at 19:37
  • @PrashantGupta As you can see State has two different number (two states). So I need to pull all records and build a string/list of values that will be store in my session scope. – espresso_coffee May 22 '18 at 19:39
  • Then your query appears just fine. Just loop through the records to fetch and store list of states in your session. – Prashant Agarwal May 22 '18 at 19:43

0 Answers0