Oracle SQL - filter out partitions or row groups that contain rows with specific value

Question

I'm trying to solve the following: the data is organized in the table with Column X as the foreign key for the information (it's the ID which identifies a set of rows in this table as belonging together in a bundle, owned by a particular entity in another table). So each distinct value of X has multiple rows associated with it here. I would like to filter out all distinct values of X that have a row associated with them containing value "ABC" in Column Q.

i.e.

data looks like this:

Column X   Column Q
--------   ---------
123        ABC
123        AAA    
123        ANQ
456        ANQ
456        PKR
579        AAA
579        XYZ
886        ABC

the query should return "456" and "579" because those two distinct values of X have no rows containing the value "ABC" in Column Q.

I was thinking of doing this with a minus function (select distinct X minus (select distinct X where Q = "ABC")), as all I want are the distinct values of X. But i was wondering if there was a more efficient way to do this that could avoid a subquery? If for example I could partition the table over X and throw out each partition that had a row with the value "ABC" in Q?

Answer 1

I prefer to answer questions like this (i.e. about groups within groups) using aggregation and the having clause. Here is the solution in this case:

select colx
from data d
group by colx
having max(case when colq = 'ABC' then 1 else 0 end) = 0

If any values of colx have ABC, then the max() expression returns 1 . . . which does not match 0.