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

'Sara' Sara Has only 'Delete' Access

and 'Maryam' has 'Read', 'Write', 'Update' and 'Delete' Access

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

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

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