0

I'm starting to learn SQL, learning about views. I was wondering if is possible to join three tables in one view, where some columns will be based on rows from of these tables.

I have three tables:

Roles:

Id | Name  | PermissionId
1  | Admin | 1
2  | Staff | 2

RolePermissions:

Id | RoleId | PermissionId
1  | 1      | 1
2  | 1      | 2
3  | 1      | 3
4  | 2      | 1
5  | 2      | 2      <- staff doesn't have permission 3

Permissions:

Id | Name
1  | Perm1
2  | Perm2
3  | Perm3
.
. (not fixed number of permissions)

I would like to create view like this:

Id (of role) | Name  | Perm1 | Perm2 | Perm3  ... (not fixed number of columns)
1            | Admin | True  | True  | True
2            | Staff | True  | True  | False

Is it possible?

tauri
  • 293
  • 2
  • 6
  • 18
  • 1
    Please tag your question with the database you are using. Also, including sample code you have tried is also helpful. – Gordon Linoff Aug 05 '15 at 10:56
  • 1
    Sure, you can pivot the second table but note ifyou have an arbitrary number of Permissions/Role you ill got a arbitrary number of columns – jean Aug 05 '15 at 10:57
  • 1
    possible duplicate of [SQl Pivot help with multiple tables](http://stackoverflow.com/questions/2914049/sql-pivot-help-with-multiple-tables) – Bulat Aug 05 '15 at 10:58
  • 1
    This is definitely a duplicate question, so you need to do some search on [sql][pivot] combination and try something first. http://stackoverflow.com/questions/tagged/sql+pivot – Bulat Aug 05 '15 at 10:59
  • Thank you. Now I know what I should look for – tauri Aug 05 '15 at 11:01
  • @jean yes, I don't know how many permissions I will have, so I cannot create fixed number of columns in my view – tauri Aug 05 '15 at 11:13
  • View cannot use dynamic sql, and therefore cannot have dynamic columns. – Tab Alleman Aug 05 '15 at 13:59

1 Answers1

1

You cannot use a view if you don't know how many columns to output. Code below should be you in the right direction. Use it in a Procedure with dynamic SQL if you need to dynamically build the list of columns

; With roles(Id, Name, PermissionID) as (
Select * From (values(1, 'Admin', '1'), (2, 'Staff', '2')) as r(Id, Name, PermissionId)
), RolePermissions(Id, RoleId, PermissionId) as (
Select * From (Values(1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 2, 1), (5, 2, 2)) as p(Id, RoleId, PermissionId)
), Permissionss(Id, Name) as (
Select * From (Values(1, 'Perm1'), (2, 'Perm2'), (3, 'Perm3')) as p(Id, Name)
), data as(
Select r.Id, rp.PermissionId, p.Name From Roles as r
Inner Join RolePermissions as rp on rp.RoleId = r.Id
Inner Join Permissionss as p on rp.PermissionId = p.Id
)
Select piv.Id as [Id of Role], r.Name
, [Perm1] = case when [Perm1] is not null then 'true' else 'false' end
, [Perm2] = case when [Perm2] is not null then 'true' else 'false' end
, [Perm3] = case when [Perm3] is not null then 'true' else 'false' end
From data
Pivot( max(PermissionId)
For Name in ( [Perm1], [Perm2], [Perm3])
) as piv
Inner Join roles as r on r.Id = Piv.Id
Julien Vavasseur
  • 3,854
  • 1
  • 20
  • 29