Get value of two instances of a secondary table

Question

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

Answer 1

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.