sql - get common rows from one table against any column

Question

I have three tables :

CREATE TABLE workflow_roles (
  role_id   NUMBER       PRIMARY KEY,
  role_desc VARCHAR2(20)
);

CREATE TABLE tbl_workflow (
  workflow_id   VARCHAR2(5)  PRIMARY KEY,
  workflow_desc VARCHAR2(20)
);

CREATE TABLE workflow_detail (
  role_id     NUMBER      REFERENCES workflow_roles( role_id ),
  workflow_id VARCHAR2(5) REFERENCES tbl_workflow( workflow_id )
);

Let say the data is:

INSERT INTO workflow_roles
  SELECT 1, 'Role 1' FROM DUAL UNION ALL
  SELECT 2, 'Role 2' FROM DUAL;

INSERT INTO tbl_workflow
  SELECT 'A', 'Work A' FROM DUAL UNION ALL
  SELECT 'B', 'Work B' FROM DUAL UNION ALL
  SELECT 'C', 'Work C' FROM DUAL UNION ALL
  SELECT 'D', 'Work D' FROM DUAL UNION ALL

INSERT INTO workflow_detail
  SELECT 1, 'A' FROM DUAL UNION ALL
  SELECT 1, 'B' FROM DUAL UNION ALL
  SELECT 2, 'B' FROM DUAL UNION ALL
  SELECT 2, 'C' FROM DUAL;

Role "B" exists in both workflows

I want to get the common roles who exists in selected workflows i.e. it should return Role B only .

I've tried the following :

select * from workflow_roles where role_id in 
(
select role_id from workflow_detail where workflow_id in (1,2)
);

But it is returning all roles assigned to the given workflows.

How can I do this ?

Answer 1

A solution using a User-Defined Aggregation function to get the intersection of multiple collections:

Oracle Setup:

First, define a collection to hold the workflows:

CREATE OR REPLACE TYPE VARCHAR2s_Table IS TABLE OF VARCHAR2(4000);
/

Secondly, define an object to use in the aggregation process:

CREATE OR REPLACE TYPE Varchar2sTableIntersection AS OBJECT(
  intersection VARCHAR2s_Table,

  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT Varchar2sTableIntersection
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT Varchar2sTableIntersection,
    value       IN     VARCHAR2s_Table
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT Varchar2sTableIntersection,
    returnValue    OUT VARCHAR2s_Table,
    flags       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT Varchar2sTableIntersection,
    ctx         IN OUT Varchar2sTableIntersection
  ) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY Varchar2sTableIntersection
IS
  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT Varchar2sTableIntersection
  ) RETURN NUMBER
  IS
  BEGIN
    ctx := Varchar2sTableIntersection( NULL );
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT Varchar2sTableIntersection,
    value       IN     VARCHAR2s_Table
  ) RETURN NUMBER
  IS
  BEGIN
    IF value IS NULL THEN
      NULL;
    ELSIF self.intersection IS NULL THEN
      self.intersection := value;
    ELSE
      self.intersection := self.intersection MULTISET INTERSECT value;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT Varchar2sTableIntersection,
    returnValue    OUT VARCHAR2s_Table,
    flags       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    returnValue := self.intersection;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT Varchar2sTableIntersection,
    ctx         IN OUT Varchar2sTableIntersection
  ) RETURN NUMBER
  IS
  BEGIN
    IF self.intersection IS NULL THEN
      self.intersection := ctx.intersection;
    ELSIF ctx.intersection IS NULL THEN
      NULL;
    ELSE
      self.intersection := self.intersection MULTISET INTERSECT ctx.intersection;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;
END;
/

Thirdly, create a user-defined aggregation function:

CREATE FUNCTION MULTISET_INTERSECT( collection VARCHAR2s_Table )
RETURN VARCHAR2s_Table
PARALLEL_ENABLE AGGREGATE USING Varchar2sTableIntersection;
/

Query 1 - Output as a collection:

SELECT MULTISET_INTERSECT( workflows ) AS common_workflows
FROM   (
  SELECT role_id,
         CAST(
           COLLECT(
             workflow_id
           ) AS VARCHAR2s_Table
         ) AS workflows
  FROM   workflow_detail
  GROUP BY role_id
);

Output:

COMMON_WORKFLOWS
----------------------
VARCHAR2S_TABLE( 'B' )

Query 2 - Output as rows:

SELECT t.COLUMN_VALUE AS common_workflows
FROM   (
         SELECT MULTISET_INTERSECT( workflows ) AS common
         FROM   (
           SELECT role_id,
                  CAST(
                    COLLECT(
                      workflow_id
                    ) AS VARCHAR2s_Table
                  ) AS workflows
           FROM   workflow_detail
           GROUP BY role_id
         )
       ) cw
       CROSS JOIN TABLE( cw.common ) t;

Output:

COMMON_WORKFLOWS
----------------
B