Elham Azadfar Elham Azadfar -4 years ago 52
SQL Question

How to query particular user in sql?

CREATE TABLE Users(UserId int,UserName nvarchar(15), Permisions 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' Premision?

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 Address" 8

2 Sara 8 Update 8

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download