1

I have two tables, users and permissions. Users have a username. Permissions have a name (e.g. cards, pages, players) and a type (e.g. read only, read/write) and a foreign key relationship to users. Both tables have an basic integer primary key.

For various reasons I would like to query all user to see the status of their cards permission and pages permission at the same time. My ideal output would be something like...

|------------------------------------------------|
| Username | Cards Permission | Pages Permission |
|------------------------------------------------|
|   Bob    |   Read / Write   |   Read Only      |

I have already thought of two ways I can do this but neither is ideal.

First, I can query twice, each time collecting User.Id, User.Username, Permission.Type. The first for cards permission and the second for pages permission. I can then correlate the two queries in the application.

SELECT u.Id, u.Username, p.Type 
  FROM User u 
  INNER JOIN Permission p ON p.UserId = u.Id
  WHERE p.Name = '?';

Second, I can query once, collecting User.Id, User.Username, Permission.Name, Permission.Type. The query would join the permissions table and take all permissions whose name is Cards or Pages (essentially I'd get 2 times as many records, each User.Id would show up twice). I could then correlate the results in the application.

SELECT u.Id, u.Username, p.Name, p.Type 
  FROM User u 
  INNER JOIN Permission p ON p.UserId = u.Id
  WHERE p.Name = 'cards' or p.Name = 'pages';

Is there any query I can use that would give me the output I desire? I'm asking mainly for my own edification. If I JOIN the permissions table twice I end up with a cross product and run out of memory (there are 1,000,000 users and 10,000,000 permissions).

UPDATE: Detailed Schema

User
----
Id bigint not null identity primary key
Username varchar(64) not null

Permission
----------
Id bigint not null identity primary key
UserId bigint not null foreign key
Name varchar(64) not null
Type int not null
-Index non-clustered non-unique Id, UserId, Name
  • SQL server 2k16 enterprise in production. – Weston Pace Jan 1 '17 at 2:25
  • 1
    Added some details – Weston Pace Jan 1 '17 at 12:16
0

It looks like you are just trying to pivot some data.

select
    u.Id
  , u.Username
  , [Cards Permission]  =max(case when p.Name='Cards'   then p.Type else null end)
  , [Pages Permission]  =max(case when p.Name='Pages'   then p.Type else null end)
  --, [Players Permission]=max(case when p.Name='Players' then p.Type else null end)
  from user u 
    inner join Permission p ON p.UserId = u.Id
  group by u.Id, u.username;

or if we want the name of the type for type integer, something like:

select
    u.Id
  , u.Username
  , [Cards Permission]  =max(case when p.Name='Cards'   then t.Name else null end)
  , [Pages Permission]  =max(case when p.Name='Pages'   then t.Name else null end)
  --, [Players Permission]=max(case when p.Name='Players' then t.Name else null end)
  from user u 
    inner join Permission p ON p.UserId = u.Id
    inner join [Type] t ON p.Type = t.Id
  group by u.Id, u.username;


If your nonclustered index on permissions is (Id, UserId, Name), it's not doing you any favors. unique nonclustered (UserId, Name, Type) or nonclustered (UserId, Name) include (Type) would be more useful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.