I have the following table:
People
---------
ID | Name
---------
1 | John
2 | Sam
And I have another table:
Permissions
-----------
ID | Perm
-----------
1 | View
2 | Edit
3 | Delete
These two tables are linked in a third table:
UserPermissions
----------------------
ID | User | Permission
----------------------
1 | 1 | 1 (View)
2 | 1 | 3 (Delete)
3 | 2 | 1 (View)
I am trying to select a "total" permissions type table, where, if I wanted to get the permission for a user (Lets say user 2 (Sam)), I would get the following table:
UserPermissions
------------------
Permission | User
------------------
1 (View) | 2
2 (Edit) | NULL (Or some other nullish value)
3 (Delete)| NULL
I have only recently started MySQL and I have no idea of what search terms I should be trying to get examples of similar queries. Does anyone know what type of queries I should be searching for / a way to implement this?
If you want to do this for one user, you want a left outer join:
select p.*, up.user;
from Permissions p left outer join
UserPermissions up
on p.Permission = up.Permission and
up.User = 2;
This works for one user.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments