>>>Hi,
>>>I think I finally got it right! I ended up with this SP (against my test tables so different name):

ALTER PROCEDURE [dbo].[GetRestriction] (@UserID INT, @ItemName VARCHAR(100))
>>>AS
>>>BEGIN
>>>SELECT 
>>>MIN(ISNULL(UIItemRestrictions.RestrictionEnum,0))
>>>FROM Users
>>>LEFT JOIN UserRoles ON Users.Id = UserRoles.UserId
>>>LEFT JOIN UIItemRestrictions ON (UIItemRestrictions.RoleId = UserRoles.RoleId)
>>>INNER JOIN UIItems ON (UIItems.Id = UIItemRestrictions.UIItemId) AND (UIItems.Name = @ItemName)
>>>WHERE Users.Id = @UserId
>>>GROUP BY Users.Id
>>>END

Then I spent more time battling to get the SPROC working from the EntityFramework - but succeeded with that as well :-}
>>>(Actually, looking at it now, I guess I didn't need to pull in the Users table but heck.....)
>>
>>
>>(1) No, I do not think you need the Users table
>>Just select from UserRoles where ( UserRoles.UserId = @UserId )
>>
>>(2) What results do you get if there's a user without any entry in the UserRoles ? (Remember Joe ?)
>>I covered that a couple of posts back
>>
>>UPDATE
>>
>>Test carefully the user without entry in the UserRoles table
>>I look at this
>>>LEFT JOIN UserRoles ON Users.Id = UserRoles.UserId
>>and I think it will end up as 0 = no restriction (due to the outer join)
>>
>>
>>Cheers,
>
>
>Actually, on the second thought - you're right, we don't need Users table.


Of course I'm right - see message#1427598