Select exact subset from table

oracle

I want to select an exact subset from a table.

DDL to create table:

CREATE TABLE groups (group_id NUMBER, activity_id number); 
INSERT INTO groups (group_id, activity_id) values (1,1);
INSERT INTO groups (group_id, activity_id) values (1,2); 
INSERT INTO groups (group_id, activity_id) values (2,1); 
INSERT INTO groups (group_id, activity_id) values (2,2); 
INSERT INTO groups (group_id, activity_id) values (2,3); 
INSERT INTO groups (group_id, activity_id) values (3,1); 
INSERT INTO groups (group_id, activity_id) values (3,2); 
INSERT INTO groups (group_id, activity_id) values (3,3); 
INSERT INTO groups (group_id, activity_id) values (3,4);

If I want the group_id of activity_ids (1, 2 and 3), then I would like only the exact match for group_id 2 to return, not 1 and 3.

I can do this with LISTAGG but wanted to see if there was a cleaner solution or suggestions if I should structure my table differently.

Best Answer

Prologue

This is enhancing my answer from your other question:

Enforce uniqueness across multiple rows

pre-19c - easy method

Calculate the hash-value of your list of activities and look it up in the group_activities#lock table.

pre-19c - dark side

Create an OPERATOR that does the comparison between two activity_list_nt values.

I consider this a "not ideal solution" because it is tempting you down the path of building your own Domain Index.

19c+

19c introduces the JSON_EQUAL operator. This makes list comparison easy.

with group_activities_dml as ( 
    select group_id, json_arrayagg( activity_id ) activity_list_json 
    from groups 
    group by group_id 
) 
select * 
from group_activities_dml 
where json_equal( activity_list_json, '[1,2]' )
Related Question