DECLARE @Matches AS int
DECLARE @Result AS int

SET @Matches =
(
    Select COUNT(*)
        FROM Houses
            INNER JOIN Houses_Access ON Houses.ID = Houses_Access.DoorKey
            FULL OUTER Join UserAccess ON  Houses_Access.GroupKey  = UserAccess.GroupKey
        WHERE Houses.ID IS NOT NULL AND Houses.ID = @DoorKey AND UserAccess.UserKey = @UserKey
)

IF(@Matches < 1)
 SET @Result = 0
ELSE IF(@Matches > 0)
    BEGIN
        SET @Matches =
        (
        Select COUNT(*)
        FROM Houses
            INNER JOIN Houses_Access ON Houses.ID = Houses_Access.DoorKey
            FULL OUTER Join UserAccess ON  Houses_Access.GroupKey  = UserAccess.GroupKey
        WHERE Houses.ID IS NOT NULL AND Houses.ID = @DoorKey AND UserAccess.UserKey = @UserKey
            AND Houses_Access.GroupKey != Houses.OwnerGroupKey
        )
        IF(@Matches < 1)
            SET @Result = 0
        ELSE
            SET @Result = 1
    END

RETURN(@Result)

END

Create View vw_HousesAccess
        as
        Select * --TODO specify columns where
        FROM Houses
            INNER JOIN Houses_Access ON Houses.ID = Houses_Access.DoorKey
            FULL OUTER Join UserAccess ON  Houses_Access.GroupKey  = UserAccess.GroupKey

        ---then in main query you can use like this.
        IF Exists(Select 1 from vw_HousesAccess  where  WHERE ID IS NOT NULL AND ID = @DoorKey AND UserKey = @UserKey)
         ---your logic