Join Two Parent tables where all children required to match

Question

I have 2 tables which share a common table of "types" The type table is as follows:

CREATE TABLE [ModifierType](
[ModifierTypeID] [int] NOT NULL,
[Code] [varchar](10) NOT NULL,
[Name] [varchar](50) NOT NULL,)

CREATE TABLE [UserRequest](
[UserRequestID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,)

CREATE TABLE [Matrix](
[MatrixID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,)

CREATE TABLE [UserRequestModifier](
[UserRequestModifierID] [int] NOT NULL,
[UserRequestID] [int] NOT NULL,
[ModifierTypeID] [int] NOT NULL,
[Value] [varchar](50) NOT NULL,)

CREATE TABLE [MatrixModifier](
[MatrixModifierID] [int] NOT NULL,
[MatrixID] [int] NOT NULL,
[ModifierTypeID] [int] NOT NULL,
[Value] [varchar](50) NOT NULL,)

The others are a UserRequestModifier table, and a "MatrixModifier" table which contains rules for determining access a request should be provisioned. Matrix in this case just refers to a set of rules that will grant access to a specific application or group. These two tables each have a ModifierTypeID which will link the two and a value field that will be used for finding matches.

However, each UserRequest/Matrix (parents to the modifier tables) can each have multiple modifier records.

What I need to do, is find all the Matrix records for which the UserRequestModifiers meets all the MatrixModifier requirements. Basically, I want to ignore any MatrixIDs that have even 1 modifier value that does not match ANY of the UserRequestModifier values.

So far I have a query that will do it, but it seems a bit backwards to me, because I'm having to first find all the MatrixIDs that the UserRequestModifiers do not meet the requirements in a subselect. Then getting the records that are NOT IN those results as follows:

SELECT 
    UR.[UserRequestModifierID]
    ,UR.[ModifierTypeID]        
    ,UR.[Value] AS [URValue]
    ,MM.[Value] AS [MMValue]
    ,MM.[MatrixModifierID]
    ,MM.[MatrixID]
    ,MM.[ModifierTypeID]        
    ,M.[MatrixID]       
FROM
    AMP.[UserRequestModifier] AS UR
LEFT OUTER JOIN AMP.[MatrixModifier] AS MM
    ON (MM.[ModifierTypeID] = UR.[ModifierTypeID])
LEFT OUTER JOIN AMP.[Matrix] AS M
WHERE
    UR.[UserRequestID] = @UserRequestID
    AND M.[MatrixID] IS NOT NULL
    AND M.[MatrixID] NOT IN
        (SELECT
            DISTINCT MM.[MatrixID]      
        FROM
            AMP.[UserRequestModifier] AS UR
        LEFT OUTER JOIN AMP.[MatrixModifier] AS MM
            ON (MM.[ModifierTypeID] = UR.[ModifierTypeID])
        WHERE
            UR.[UserRequestID] = @UserRequestID
            AND (CASE WHEN LTRIM(RTRIM(MM.[Value])) = LTRIM(RTRIM(UR.[Value])) THEN 1 ELSE 0 END) = 0
            AND MM.[MatrixID] IS NOT NULL)
ORDER BY M.[MatrixID], MM.[ModifierTypeID]

I know this is a bit hard to follow, but I'm hoping someone can point out something obvious I'm missing.

Answer 1

For MS SQL Server:

-- ====================
-- sample data
-- ====================
DECLARE @ModifierType TABLE (
[ModifierTypeID] [int] NOT NULL,
[Code] [varchar](10) NOT NULL,
[Name] [varchar](50) NOT NULL)

DECLARE @UserRequest TABLE (
[UserRequestID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL)

DECLARE @Matrix TABLE (
[MatrixID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL)

DECLARE @UserRequestModifier TABLE (
[UserRequestModifierID] [int] NOT NULL,
[UserRequestID] [int] NOT NULL,
[ModifierTypeID] [int] NOT NULL,
[Value] [varchar](50) NOT NULL)

DECLARE @MatrixModifier TABLE (
[MatrixModifierID] [int] NOT NULL,
[MatrixID] [int] NOT NULL,
[ModifierTypeID] [int] NOT NULL,
[Value] [varchar](50) NOT NULL)

insert into @modifiertype
select 1, '1', 'modname1'
union all
select 2, '2', 'modname2'
union all
select 3, '3', 'modname3'
union all
select 4, '4', 'modname4'
union all
select 5, '5', 'modname5'
union all
select 6, '6', 'modname6'

insert into @userrequest
select 1, 'ureq1'
union all
select 2, 'ureq2'
union all
select 3, 'ureq3'
union all
select 4, 'ureq4'

insert into @matrix
select 1, 'm1'
union all
select 2, 'm2'
union all
select 3, 'm3'
union all
select 4, 'm4'


insert into @userrequestmodifier
select 1, 1, 1, 'val1'
union all
select 2, 1, 2, 'val2'
union all
select 3, 1, 3, 'val3'
union all
select 4, 1, 4, 'val4'
union all
select 5, 2, 5, 'val5'
union all
select 6, 1, 5, 'val5'
union all
select 7, 1, 6, 'val6'
union all
select 8, 1, 6, 'val6'




insert into @matrixmodifier
select 1, 1, 1, 'val1'
union all
select 2, 2, 2, 'val2'
union all
select 3, 3, 3, 'val'
union all
select 4, 2, 4, 'val4'
union all
select 5, 2, 5, 'val5'
union all
select 6, 3, 4, 'val4'
union all
select 7, 13, 4, 'val4'

declare @UserRequestID int
set @UserRequestID = 1


-- ====================
-- solution
-- ====================
select
    userrequestmodifierid,
    modifiertypeid,
    urvalue,
    mmvalue,
    matrixmodifierid,
    mmmatrixid,
    mmatrixid
from
(
    select 
        urm.userrequestmodifierid,
        urm.modifiertypeid,
        urvalue = urm.value,
        mmvalue = mm.value,
        mm.matrixmodifierid,
        mmmatrixid = mm.matrixid,
        mmatrixid = m.matrixid,
        f = max(case when urm.value != mm.value then 1 end) over (partition by mm.matrixid)
    from @userrequestmodifier urm
    left join @matrixmodifier mm on
        urm.modifiertypeid = mm.modifiertypeid
    left join @matrix m on
        m.matrixid = mm.matrixid
    where urm.userrequestid = @userrequestid
) t
where f is null or mmatrixid is null