Find all rows with the same exact relations as provided in another table

Question

Given these tables:

---

Table: Test

Columns:

• testID int PK
• name nvarchar(128) UNIQUE NOT NULL

---

Table: [Test-Inputs]

Columns

• inputsTableName nvarchar(128) UNIQUE PK
• testID int PK FK

---

Temporary Table: ##TestSearchParams

Columns:

• inputsTableName nvarchar(128) UNIQUE NOT NULL

---

I need to find Tests that have entries in Test-Inputs with inputsTableNames matching EXACTLY ALL of the entries in ##TestSearchParams; the resulting tests relationships must be exactly the ones listed in ##TestSearchParams.

Essentially I am finding tests with ONLY the given relationships, no more, no less. I am matching names with LIKE and wildcards, but that is a sidenote that I believe I can solve after the core logic is there for exact matching.

This is my current query:

Select *
From   Tests As B
Where  B.testID In (
                       Select ti
                       From   (
                                  Select   (
                                               Select Count(inputsTableName)
                                               From   [Test-Inputs]
                                               Where  [Test-Inputs].testID = B.testID
                                           ) - Count(Distinct i1) As delta,
                                           ti
                                  From     (
                                               Select [Test-Inputs].inputsTableName As i1,
                                                      [Test-Inputs].testID As ti
                                               From   ##TableSearchParams
                                               Join   [Test-Inputs]
                                                   On [Test-Inputs].inputsTableName Like ##TableSearchParams.inputsTableName
                                                      And B.testID = [Test-Inputs].testID
                                           ) As A
                                  Group By ti
                              ) As D
                       Where  D.delta = 0
                   );

The current problem is that his seems to retrieve Tests with a match to ANY of the entries in ##TableSearchParams. I have tried several other queries before this, to varying levels of success. I have working queries for find tests that match any of the parameters, all of the paramters, and none of the parameters -- I just cant get this query working.

Here are some sample table values:

Tests

• 1, Test1
• 2, Test2
• 3, Test3

[Test-Inputs]

• Table1, 1
• Table2, 2
• Table1, 3
• Table2, 3

TestSearchParams

• Table1
• Table2

---

The given values should only return (3, Test3)

Answer 1

Here's a possible solution that works by getting the complete set of TestInputs for each record in Tests, left-joining to the set of search parameters, and then aggregating the results by test and making two observations:

First, if a record from Tests includes a TestInput that is not among the search parameters, then that record must be excluded from the result set. We can check this by seeing if there is any case in which the left-join described above did not produce a match in the search parameters table.

Second, if a record from Tests satisfies the first condition, then we know that it doesn't have any superfluous TestInput records, so the only problem it could have is if there exists a search parameter that is not among its TestInputs. If that is so, then the number of records we've aggregated for that Test will be less than the total number of search parameters.

I have made the assumption here that you don't have Tests records with duplicate TestInputs, and that you likewise don't use duplicate search parameters. If those assumptions are not valid then this becomes more complicated. But if they are, then this ought to work:

declare @Tests table (testID int, [name] nvarchar(128));
declare @TestInputs table (testID int, inputsTableName nvarchar(128));
declare @TestSearchParams table (inputsTableName nvarchar(128));

-- Sample data.
--
-- testID 1 has only a subset of the search parameters.
-- testID 2 matches the search parameters exactly.
-- testID 3 has a superset of the search parameters.
--
-- Therefore the result set should include testID 2 only.
insert @Tests values
    (1, 'Table A'), 
    (2, 'Table B'), 
    (3, 'Table C');
insert @TestInputs values
    (1, 'X'),
    (2, 'X'),
    (2, 'Y'),
    (3, 'X'),
    (3, 'Y'),
    (3, 'Z');
insert @TestSearchParams values 
    ('X'), 
    ('Y');

declare @ParamCount int;
select @ParamCount = count(1) from @TestSearchParams;

select
    Tests.testID,
    Tests.[name]
from
    @Tests Tests
    inner join @TestInputs Inputs on Tests.testID = Inputs.testID
    left join @TestSearchParams Search on Inputs.inputsTableName = Search.inputsTableName
group by
    Tests.testID,
    Tests.[name]
having
    -- If a group includes any record where Search.inputsTableName is null, it means that
    -- the record in Tests has a TestInput that is not among the search parameters.
    sum(case when Search.inputsTableName is null then 1 else 0 end) = 0 and

    -- If a group includes fewer records than there are search parameters, it means that
    -- there exists some parameter that was not found among the Tests record's TestInputs.
    count(1) = @ParamCount;