Объединение двух родительских таблиц, в которых все дочерние элементы должны совпадать

ProgramBox

Объединение двух родительских таблиц, в которых все дочерние элементы должны совпадать

Post author:admin
Запись опубликована:5 февраля, 2023
Post category:Вопросы по программированию

#sql #join #relational-division

#sql #Присоединиться #реляционное разделение

Вопрос:

У меня есть 2 таблицы, которые имеют общую таблицу «типов», таблица типов выглядит следующим образом:

 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,)

Остальные — это таблица UserRequestModifier и таблица «MatrixModifier», которая содержит правила для определения доступа, который должен быть предоставлен запросу. Матрица в этом случае просто ссылается на набор правил, которые будут предоставлять доступ к определенному приложению или группе. Каждая из этих двух таблиц имеет ModifierTypeID, который свяжет две таблицы, и поле значения, которое будет использоваться для поиска совпадений.

Однако каждый запрос пользователя / матрица (родители таблиц-модификаторов) может иметь несколько записей-модификаторов.

Что мне нужно сделать, это найти все записи матрицы, для которых UserRequestModifiers удовлетворяет всем требованиям MatrixModifier. По сути, я хочу игнорировать любые идентификаторы матрицы, которые имеют даже 1 значение модификатора, которое не соответствует НИ ОДНОМУ из значений UserRequestModifier .

Пока у меня есть запрос, который это сделает, но мне кажется, что это немного назад, потому что мне нужно сначала найти все идентификаторы матрицы, которые UserRequestModifiers не соответствуют требованиям в подвыборке. Затем получаем записи, которых НЕТ В этих результатах, следующим образом:

 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]

Я знаю, что это немного сложно понять, но я надеюсь, что кто-нибудь может указать на что-то очевидное, чего мне не хватает.

Ответ №1:

Как указал Мартин Смит, это проблема реляционного разделения. Я думаю, что один из способов решить эту проблему заключается в следующем:

 SELECT
    M.*
FROM
    Matrix AS M
  LEFT JOIN
    MatrixModifier AS MM
        ON MM.MatrixID = M.MatrixID
  LEFT JOIN 
    UserRequestModifier AS URM
        ON URM.ModifierTypeID = MM.ModifierTypeID
GROUP BY
    M.MatrixID
HAVING
    COUNT(DISTINCT MM.ModifierTypeID) = COUNT(DISTINCT URM.ModifierTypeID)

1. @djacobson: Спасибо за редактирование. Иногда моя грамматика больше похожа на внешность Йоды. Особенно когда мне нужен сон.

2. Это почти то, что мне нужно, но часть, которую я не могу понять, что усложняет задачу (по крайней мере, для меня), заключается в том, что мне нужны не только результаты, в которых совпадают идентификаторы ModifierTypeIDs, но и только те, в которых столбцы значений совпадают со ВСЕМИ значениями MatrixModifier — исключая те, которые являютсяотсутствует даже одна.

Ответ №2:

Для 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