CODEWITHSUNDEEP
sqloracle-database
OutOfMind
OutOfMind

Reputation: 924

Oracle query to match all values in the list among all rows in table

I have a table structure as under:

ID    A     B
--   ---  -----
 1    a     a1
 2    b     b1
 3    a     a2
 4    c     a1
 5    b     b2

I want such values from column A, who are related to ALL the values of B in a list.

For Example:

I have a list of A's:

{a1,a2}

Output should be a,
c is NOT returned in the result because it is related to a1 only and not a2.

Is there a way to get this result by a SQL query?

Edited

It should work in this special case:

ID    A     B
--   ---  -----
 1    a     a1
 2    b     b1
 3    a     a2
 4    c     a1
 5    b     b2
 6    c     a3
 7    c     a2

Now c is also related to a2 and a3, but it should NOT be returned because for being a part of result, c should be related to exactly a1 AND a2

But if I query as follows:

SELECT A
FROM   table_name
WHERE  B IN ( 'a1', 'a2' )      -- The items in the list
GROUP BY A
HAVING COUNT( DISTINCT b ) = 2;

It returns c too.

Upvotes: 4

Views: 5214

Answers (3)

MT0
MT0

Reputation: 167982

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;

Upvotes: 6

LamarSt
LamarSt

Reputation: 37

SELECT DISTINCT A      
FROM tablename   
WHERE B = 'a1'
OR B = 'a2'

I think this should do the trick. Distinct is added so the value 'a' in A will only display once.

Upvotes: -1

Matt
Matt

Reputation: 15071

Use a GROUP BY and a COUNT.

SELECT A
FROM yourtable
WHERE B IN ('a1', 'a2')
GROUP BY A
HAVING Count(DISTINCT B) > 1

Upvotes: 0

Related Questions