>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