Oracle SQL - filter out sections or groups of rows that contain rows with a specific value

Question

I am trying to solve the following: the data is organized in a table with column X as a foreign key for the information (this is an identifier that identifies the set of rows in this table as belonging together in a set, belonging to a specific object in another table). Therefore, each individual value of X has several lines associated with it here. I would like to filter out all the different X values ​​that have a row associated with them containing the value "ABC" in column Q.

i.e.

The data is as follows:

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 these two different X values ​​do not have rows containing the value "ABC" in column Q.

I thought about this with a minus function (choose a great X minus (choose a great X, where Q = "ABC"), since all I want are different values ​​of X. But I was wondering, a more efficient way to do this, to avoid a subquery? If, for example, I could split the table into X and throw out each section 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 such questions (i.e. about groups within groups) using the aggregation and the having . 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 colx values ​​have ABC , then max() 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 the following:

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

SQL Fiddle