SQL - Get boolean values for each matching field

Question

I need help with SQL request.

I have 3 tables:

Table User

id  name
1   Jon
2   Jack
3   Bill

Table Type

id  name
1   View
2   Edit
3   Delete

Table Right

id  user  type
1   1     1
2   1     2
3   1     3
4   2     1
5   3     1

So table Right contains linked pairs of user-type. I need a request which gets user name, and a boolean (BIT) value for each enrty in table Type, which exists in Right table for this user. Something like this for my example tables:

Username  View  Edit  Delete
Jon       1     1     1
Jack      1     0     0 
Bill      1     0     0

Thank you very much in advance!

Is this SQL Server or MySQL? They are completely different products. — Zane, Mar 06 '15 at 20:40

koriander · Answer 1 · 2015-03-06T23:02:05.437

untested:

select name,
       coalesce(select 1 from `right` where `type` = 1 and right.user = user.id, 0) as `View`,
       coalesce(select 1 from `right` where `type` = 2 and right.user = user.id, 0) as `Edit`,
       coalesce(select 1 from `right` where `type` = 3 and right.user = user.id, 0) as `Delete`
from User

Alternatively:

select name, coalesce(RVIEW.R, 0) as `View`, coalesce(REDIT.R, 0) as `Edit`, coalesce(RDEL.R, 0) as `Delete`
from User
left join (select 1 R from `right` where `type` = 1) RVIEW on (right.user = user.id)
left join (select 1 R from `right` where `type` = 2) REDIT on (right.user = user.id)
left join (select 1 R from `right` where `type` = 3) RDEL on (right.user = user.id)

score 0 · Answer 2 · edited May 23 '17 at 11:50

In your example, you are using reserved words as table names.

If you want to learn more about naming conventions for table names, have a look at the links in an earlier question on Stack Overflow here

Example below shows yet another way of getting the data you want (with other names for the tables):

select  person.name as Username
,       max( if( person_right.type_id = 1, 1, 0 ) ) as `View`
,       max( if( person_right.type_id = 2, 1, 0 ) ) as `Edit`
,       max( if( person_right.type_id = 3, 1, 0 ) ) as `Delete`

from person

left outer join person_right
on person_right.user_id = person.id

group by person.name
order by person.id

Another thing that might be worth looking at is the datamodel, because Rights are normally quite "fixed".

If anyone accidentally changes one of the names in the Type table, you might have a serious security issue.

What you can do is change the person_right table to look like this

windowid  user_id  view_access  edit_access  delete_access
1         1        1            1            1
1         2        1            0            0
1         3        1            0            0

where the primary key would be window_id+user_id allowing you to setup different rights per user in a particular window/part of your application.

Hope this helps.

SQL - Get boolean values for each matching field

2 Answers2