SELECT all distinct rows with two simple characteristic-CodePudding

I have this table in an Oracle 18c database:

CREATE TABLE TABLEA
(
  COLUMNA  NUMBER(5)                            NOT NULL,
  COLUMNB  NUMBER(5)                            NOT NULL
)

I have these rows:

Insert into TABLEA
   (COLUMNA, COLUMNB)
 Values
   (96, 1011);
Insert into TABLEA
   (COLUMNA, COLUMNB)
 Values
   (96, 9130);
Insert into TABLEA
   (COLUMNA, COLUMNB)
 Values
   (848, 1011);
Insert into TABLEA
   (COLUMNA, COLUMNB)
 Values
   (848, 1172);
Insert into TABLEA
   (COLUMNA, COLUMNB)
 Values
   (1095, 1011);
Insert into TABLEA
   (COLUMNA, COLUMNB)
 Values
   (1095, 1172);

It should be very simple but I really stuck here. I need to get all the different values of COLUMNA, when all COLUMNB from the same COLUMNA are in (1172, 1011, 1037).

Result expected:

   COLUMNA 
---------- 
       848       
      1095

The value 96 shouldn't be returned, because there is a row with a value in COLUMNB that isn't in the condition.

Those values (1172, 1011, 1037) can be in a specific table with a single column if it helps.

I've tried this, but I just want the distinct values:

  SELECT columnA, COUNT (*) OVER (PARTITION BY columnA)
    FROM tableA
   WHERE EXISTS
             (SELECT 1
                FROM tableB -- With (1172, 1011, 1037) in each row
               WHERE COLUMNB = values)
GROUP BY COLUMNA
  HAVING COUNT (*) > 1;

Is there any other solution?

CodePudding user response：

Maybe you should use DISTINCT to show non repeated values.

Have you tried something like this?

SELECT DISTINCT columnA FROM tableA WHERE columnB in (1172, 1011, 1037)

CodePudding user response：

Maybe something like so, not 100% it's late here :)

select
columna
from
#tablea
group by columna
having count(*)=sum(case when columnb in (1172, 1011, 1037) then 1 else 0 end)