CODEWITHSUNDEEP
sqloracle-databasejoinself-join
RNJ
RNJ

Reputation: 15552

SQL left join for users table

I have the following example table (simplified from what I really have as there are a few more joins in there)

|permission|username|
| perm1    | u1     |
| perm2    | u1     |
| perm3    | u1     |
| perm1    | u2     |
| perm4    | u2     |

I have many users and many groups in this table

I am having trouble with user permissions where some users can do things in my application and others cannot. At the moment I am having to manually go through and figure out what the differences are.

I want to crate a query from the above table that gives me an answer like this

|permission| u1 | u2 |
|perm1     | 1  | 1  |
|perm2     | 1  | 0  |
|perm3     | 1  | 0  |
|perm4     | 0  | 1  |

I do not really want 1 and 0 under the users but I want something to I can easily visualise that user 1 has these permissions and user2 does not. This would be really useful for me to ensure that users have the same permissions.

I have done the SQL to get the top table so I can see the permissions.

I was thinking that some sort of left join on itself might the answer there but everything I try seems to end up as invalid SQL.

Any hints please? Im happy to experiment if I get a starting point :)

Thanks in advance

Upvotes: 2

Views: 109

Answers (1)

Andomar
Andomar

Reputation: 238176

What you're looking for is called "pivoting". One way to do that is a group by, like:

select  permission
,       count(case when username = 'u1' then 1 end) as User1
,       count(case when username = 'u2' then 1 end) as User2
,       count(case when username = 'u3' then 1 end) as User3
,       ...
from    YourTable
group by
        permission

It looks like Oracle supports the PIVOT keyword. You can use it to accomplish the same thing:

select  *
from    YourTable
pivot   (
        count(*) as HasPermission
        for (username) in ('u1', 'u2', 'u3', ...)
        );

Example for both approaches at SQL Fiddle.

Upvotes: 3

Related Questions