>>>Hi,
>>>
>>>I'm looking at designing a DB for tracking which users are allowed access to various form controls. The basic tables are:
>>>
>>>Users - UserRoles - Roles - UIItemRestrictions - UIItems.
>>>The UIItemRestrictions table has an int 'Restrictions' column indicating the level of restriction placed on the item for the role - the lower the int the greater the access (for simplicity assume 2 might mean don't show it, 1 show readonly and 0 no restriction). Since a user can have multiple roles I've got this code the detect the least restriction placed on a user for the relevant item:

SELECT  dbo.Users.Name AS UserName, dbo.UIItems.Name AS ItemName, MIN(DISTINCT dbo.UIItemRestrictions.RestrictionEnum) AS RestrictionEnum, 
>>>                      dbo.Users.Id AS UserId, dbo.UIItems.Id AS ItemId
>>>FROM         dbo.Roles INNER JOIN
>>>                      dbo.UIItemRestrictions ON dbo.Roles.Id = dbo.UIItemRestrictions.RoleId INNER JOIN
>>>                      dbo.UIItems ON dbo.UIItemRestrictions.UIItemId = dbo.UIItems.Id INNER JOIN
>>>                      dbo.UserRoles ON dbo.Roles.Id = dbo.UserRoles.RoleId INNER JOIN
>>>                      dbo.Users ON dbo.UserRoles.UserId = dbo.Users.Id
>>>GROUP BY dbo.Users.Name, dbo.UIItems.Name, dbo.Users.Id, dbo.UIItems.Id

>>>But I don't want to have to create entries in UIItemRestrictions to indicate that there is no restriction. How would I create a query that will return a restriction of 0 if one of the roles to which a user belongs doesn't have an entry in the RoleItems table?
>>>
>>>Hope this is clear enough :-}
>>>TIA,
>>>Viv
>>
>>
>>Viv,
>>
>>(1) Use an outer join
>>(2) convert the absence (null) to zero
>>
>>Small sample - you come out with 0
>>
>>

>>declare @User table (u_id	   Int,
>>                     u_name	varchar(20),
>>                     u_role_id	int 
>>                     )
>>               
>>insert into @User
>>select  1, 'Joe', 1
>>union 
>>select 2, 'Viv', 2
>>
>>
>>declare @Role table ( role_id	int, 
>>		  role_restriction int 
>>	  )
>>insert into @Role
>>select 1, 4
>>union 
>>select 1, 5
>>
>>select u_id,
>>	u_name,
>>	Isnull(MIN(role_restriction), 0)
>>	from @User 
>>		left join @Role on (u_role_id = role_id )
>>		group by u_id, u_name
>> 
>>

>>
>>Output
>>

>>1	Joe	4
>>2	Viv	0
>>

>
>Thanks. I think that put me on the right track although my structure is more complex. This seems to work - can you see any problems?

declare @User table (u_id  Int, u_name varchar(20))
>insert into @User
>select  1, 'Viv'
>union
>select 2, 'Joe'
>
>declare @Role table ( r_Id	int, r_name	varchar(20)
>	  )
>insert into @Role
>select 1, 'Admin'
>union 
>select 2, 'User'
>
>/* Put Viv in both Admin and User Roles */
>declare @UserRoles table (ur_UserId int, ur_RoleId int)
>insert into @UserRoles
>select 1,1
>union
>select 1,2
>
>declare @Items table (i_Id int)
>insert into @Items
>select 1
>
>/* Restrict User role in Item 1 */
>declare @RoleItems table (ri_RoleId int, ri_ItemId int, ri_Restriction int)
>insert into @RoleItems
>select 2,1,2
>
>select u_id,
>	u_name,
>	Isnull(MIN(ri_Restriction), 0)
>	from @User 
>		inner join @Role on (u_Id = r_Id)
>        left join @RoleItems on (r_Id = ri_RoleId)
>        left join @Items on (i_id = ri_ItemId)
>group by u_id,u_name

>
>I think I hate SQL !


Viv,

I may be misunderstanting but I do not think that your output is what you had in mind

Why do you join the User with the Role without navigating via UserRoles ?

Your output is

1	Viv	0
2	Joe	2

Looking at the 'model' I would navigate as follows
User >> UserRole >> RoleItems

And give the minimum restriction per User and Role

As far as I can see, Joe cannot do anything at all - since he has no role

select u_id,
	u_name,
	ur_RoleId,
	r_name, 
	Isnull(MIN(ri_Restriction), 0) as restriction
	from @User 
		join @UserRoles on ( u_id = ur_UserId ) 
		join @Role on ( ur_RoleId = r_Id )  /* this is just to see the description of the role */
		left join @RoleItems  on  (ur_RoleId = ri_ItemId )
		group by u_id, u_name, ur_RoleId, r_name

output

1	Viv	1	Admin	2
1	Viv	2	User	0

If I change the Role table - just add another role

insert into @Role
select 1, 'Admin'
union 
select 2, 'User'
union 
select 3, 'System'

The output would be the same, since you do not have the 'System' role

If I then add you to the System role

insert into @UserRoles
select 1,1
union
select 1,2
union
select 1,3

The output becomes

1	Viv	1	Admin	2
1	Viv	2	User	0
1	Viv	3	System	0