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

This should work:

SELECT DISTINCT t.ColX
FROM mytable t
  LEFT JOIN mytable t2 on t.colx = t2.colx and t2.colq = 'ABC'
WHERE t2.colx IS NULL

And here is the SQL Fiddle.

Good luck.

Answer 2

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.

Answer 3

How about this, using IN?

SQLFIDDLE DEMO

select distinct colx from 
demo
where colx not in (
  SELECT COLX from demo
where colq = 'ABC')
;

| COLX |
--------
|  456 |
|  579 |

Answer 4

Try this:

select DISTINCT colx 
  from demo 
 where colq not like '%A%' 
   AND colq not like '%B%' 
   AND colx not like '%C%'

SQL Fiddle