Find Id Of Parent Where All Children Exactly Match

The Scenario Let's suppose we have a set of database tables that represent four key concepts: Entity Types (e.g. account, client, etc.) Entities (e.g. instances of the above Entit

Solution 1:

This scenario can be achieve by adding compound condition in the WHERE clause since you're comparing to a pair value. Then you have to count the result based from the conditions set in the WHERE clause as well as the total rows by of the cohort_id.

SELECT  c.cohort_id
FROM    cohort c
        INNERJOIN cohort_member cm
            ON c.cohort_id = cm.cohort_id
        INNERJOIN entity e
            ON cm.entity_id = e.entity_id
WHERE   (e.entity_type_id =1AND e.business_key ='acc1')      -- condition hereOR (e.entity_type_id =1AND e.business_key ='acc2')
GROUPBY c.cohort_id
HAVINGCOUNT(*) =2-- number must be the same to the total number of conditionAND (SELECTCOUNT(*) 
             FROM cohort_member cm2 
             WHERE cm2.cohort_id = c.cohort_id) =2-- number must be the same to the total number of condition

• Test Case #1
• Test Case #2
• Test Case #3
• Test Case #4
• Test Case #5
• Test Case #6

As you can see in the test cases above, the value in the filter depends on the number of conditions in the WHERE clause. It would be advisable to create a dynamic query on this.

UPDATE

If the table test_cohort contains only one scenario, then this will suffice your requirement, however, if test_cohort contains list of scenarios then you might want to look in the other answer since this solution does not alter any table schema.

SELECT  c.cohort_id
FROM    cohort c
        INNERJOIN cohort_member cm
            ON c.cohort_id = cm.cohort_id
        INNERJOIN entity e
            ON cm.entity_id = e.entity_id
        INNERJOIN test_cohort tc
            ON tc.business_key = e.business_key
                AND tc.entity_type_id = e.entity_type_id
GROUPBY c.cohort_id
HAVINGCOUNT(*) = (SELECTCOUNT(*) FROM test_cohort)
        AND (SELECTCOUNT(*) 
             FROM cohort_member cm2 
             WHERE cm2.cohort_id = c.cohort_id) = (SELECTCOUNT(*) FROM test_cohort)

• Test Case #1
• Test Case #2
• Test Case #3
• Test Case #4
• Test Case #5
• Test Case #6

Solution 2:

I have added a column i to your test_cohort table, so that you can test all your scenarios at the same time. Here is a DDL

CREATETABLE test_cohort (
i int,
business_key NVARCHAR(255),
entity_type_id INT
);

INSERTINTO test_cohort VALUES
(1, 'acc1', 1), (1, 'acc2', 1) -- TEST #1: should match against cohort 1
,(2, 'cli1', 2), (2, 'cli2', 2) -- TEST #2: should match against cohort 2
,(3, 'cli1', 2) -- TEST #3: should match against cohort 3
,(4, 'acc1', 1), (4, 'acc2', 1), (4, 'cli1', 2), (4, 'cli2', 2) -- TEST #4: should match against cohort 4
,(5, 'acc1', 1), (5, 'cli2', 2) -- TEST #5: should match against cohort 5
,(6, 'acc1', 3), (6, 'cli2', 3) -- TEST #6: should not match any cohort

And the query:

select
    c.i, m.cohort_id
from
    (
        select*, cnt =count(*) over (partitionby i)
        from 
            test_cohort
    ) c
    join entity e on c.entity_type_id = e.entity_type_id and c.business_key = e.business_key
    join (
        select*, cnt =count(*) over (partitionby cohort_id)
        from
            cohort_member
    ) m on e.entity_id = m.entity_id and c.cnt = m.cnt
groupby m.cohort_id, c.cnt, c.i
havingcount(*) = c.cnt

Output

i   cohort_id
------------
1122334455

The idea is to count number of rows before join. And compare by exact match