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