CODEWITHSUNDEEP
sqlsql-servert-sql
Elham Azadfar
Elham Azadfar

Reputation: 737

How to query particular user in SQL?

CREATE TABLE Users
(
     UserId int,
     UserName nvarchar(15), 
     Permissions int
)

CREATE TABLE Permissions
(
     PermissionId int,
     Title NVARCHAR(15)
)

I have this Permissions table with this data:

Id  Title
--------------
1   'Read'
2   'Write'
4   'Update'
8   'Delete'

And Users table

Id  UserName    Permission
-----------------------------------
1   'David'     3
2   'Sara'      8
3   'Maryam'    15

in this case 'David' has 'Read' and 'Write' Access (1+2=3), 'Sara' Sara Has only 'Delete' Access(8) and 'Maryam' has 'Read', 'Write', 'Update' and 'Delete' Access(1+2+4+8=15)

How to know that David has 'Read' and 'Write' permission, or Sara has only 'Delete' permission?

Something like this, but in one query

with CTE as
(
     select a.Title, a.id + b.id as p_level
     from p a
     cross join p b
)
select u.*, p.*
from u u
inner join CTE p on u.permission = p.p_level 
where u.id = 1

IF @@ROWCOUNT = 0
BEGIN
with CTE as
(
select p.*, sum(id) over (order by id) as p_level
from p 
)
select u.*, p.*
from u 
inner join CTE p
on u.permission >= p.p_level WHERE u.id=1
END

Output (when id = 1): "David has Read and Write access" 1+2=3

1   DAvid   3   Read    3
1   DAvid   3   Write   3

Output (when id = 2): "Sara has only delete access" 8

2   Sara    8   Update  8

Upvotes: 0

Views: 105

Answers (2)

user4350038
user4350038

Reputation: 21

Select  U.UserId , P.PermissionId 
From    #Users As U
Inner join
        #Permissions As P
On      P.PermissionId % U.Permisions >= 1
Where   U.Permisions - P.PermissionId > 0

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can get the permissions using a join:

select u.userId, p.title
from users u join
     permissions p
     on (u.permission & p.id) > 0;

This puts the value in separate rows.

You can get them as a list using the trick:

select u.*,
       stuff( (select ',' + p.title
               from permissions p
               where (p.id & u.permission) > 0
               for xml path ('')
              ), 1, 1, ''
            ) as permissions
from users u;

Upvotes: 4

Related Questions