Oracle Setup:
CREATE TABLE table_name ( ID, A, B ) AS
SELECT 1, 'a', 'a1' FROM DUAL UNION ALL
SELECT 2, 'b', 'b1' FROM DUAL UNION ALL
SELECT 3, 'a', 'a2' FROM DUAL UNION ALL
SELECT 4, 'c', 'a1' FROM DUAL UNION ALL
SELECT 5, 'b', 'b2' FROM DUAL;
Query – Use GROUP BY and COUNT( DISTINCT ... ):
SELECT A
FROM table_name
WHERE B IN ( 'a1', 'a2' ) -- The items in the list
GROUP BY A
HAVING COUNT( DISTINCT b ) = 2; -- The number of items in the list
Output:
A
-
a
Query – Passing the list dynamically:
CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(10);
/
SELECT A
FROM table_name
WHERE B MEMBER OF :your_list
GROUP BY A
HAVING COUNT( DISTINCT B ) = CARDINALITY( :your_list );
Where the bind variable :your_list is of type stringlist.
If the list is passed as a delimited string then you can use any of the techniques in the Splitting delimited strings documentation page to separate it. There is a simple PL/SQL function that would return it as a collection that could be plugged into the above query.
Update:
SELECT A
FROM table_name
GROUP BY A
HAVING COUNT( DISTINCT CASE WHEN b IN ( 'a1', 'a2' ) THEN b END ) = 2
AND COUNT( DISTINCT CASE WHEN b NOT IN ( 'a1', 'a2' ) THEN b END ) = 0;
or
SELECT A
FROM table_name
GROUP BY A
HAVING COUNT( DISTINCT CASE WHEN b MEMBER OF :your_list THEN b END ) = CARDINALITY( :your_list )
AND COUNT( DISTINCT CASE WHEN b NOT MEMBER OF :your_list THEN b END ) = 0;