TRIEZEREN TRIEZEREN - 1 month ago 6
SQL Question

Find Common Values in VIew

using oracle database 12
I have this data

X1 X2 X3
42858 RRR08401000A 1
42858 RRR06113000A 1
42858 RRR05529000A 1
42858 RRR05529000B 1
42858 RRR49110RRRF 1
42858 34100000000A 1
42974 XYZ39916000A 1
42974 XYZ00949000B 2
42974 XYZ04888000A 1
42974 XYZ39915000B 1
42974 XYZ38535000A 1
42974 XYZ42350000A 1
42974 XYZ39746X0AA 1
42974 XYZ39743X0AA 1
42974 XYZ39923000A 1
42974 XYZ39745X0AA 1
42974 XYZ39744000A 1
42974 XYZ03058X0AB 1
42974 XYZ39759000A 1
42974 XYZ39760000B 1
42974 XYZ39762000A 1
42974 XYZ39748000A 1
42974 XYZ39749000B 1
42974 XYZ39747000A 1
42974 XYZ39742X0AA 1
42974 XYZ47965000B 1
42974 IUYI0902000E 1
42974 IUYI0897000F 1
42974 IUYI0909000F 1
42975 XYZ39916000A 1
42975 XYZ00949000B 2
42975 XYZ04888000A 1
42975 XYZ39915000B 1
42975 XYZ38535000A 1
42975 XYZ42350000A 1
42975 XYZ39746X0AA 1
42975 XYZ39743X0AA 1
42975 XYZ39923000A 1
42975 XYZ39745X0AA 1
42975 XYZ39744000A 1
42975 XYZ03058X0AB 1
42975 XYZ39759000A 1
42975 XYZ39760000B 1
42975 XYZ39762000A 1
42975 XYZ39748000A 1
42975 XYZ39749000B 1
42975 XYZ39747000A 1
42975 XYZ39742X0AA 1
42975 XYZ47965000B 1
42975 IUYI0902000E 1
42975 IUYI0897000F 1
42975 IUYI0909000F 1


What I want is to find common value in X2, then return X1
in this case, I want to get 42974 and 42975 because they shared the same X2

I use this query but it doesn't return anything

SELECT
TT.X1
FROM TEST12 TT
group by TT.WORK_ORDER
having count(distinct TT.X2) = (select count (distinct tt2.X2) from TEST12 tt2)


but if I removed 42858 from the data, it returns 42974 and 42975

Please advise

thanks,
Journey Man

cREATE TABLE TEST12 (
X1 NUMBER(8),
X2 VARCHAR2(30),
x3 NUMBER(8)
);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42858,'RRR08401000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42858,'RRR06113000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42858,'RRR05529000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42858,'RRR05529000B',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42858,'RRR49110RRRF',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42858,'34100000000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ39916000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ00949000B',2);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ04888000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ39915000B',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ38535000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ42350000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ39746X0AA',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ39743X0AA',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ39923000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ39745X0AA',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ39744000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ03058X0AB',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ39759000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ39760000B',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ39762000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ39748000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ39749000B',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ39747000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ39742X0AA',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'XYZ47965000B',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'IUYI0902000E',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'IUYI0897000F',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42974,'IUYI0909000F',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ39916000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ00949000B',2);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ04888000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ39915000B',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ38535000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ42350000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ39746X0AA',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ39743X0AA',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ39923000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ39745X0AA',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ39744000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ03058X0AB',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ39759000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ39760000B',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ39762000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ39748000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ39749000B',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ39747000A',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ39742X0AA',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'XYZ47965000B',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'IUYI0902000E',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'IUYI0897000F',1);
INSERT INTO TEST12 (X1, X2, X3) VALUES (42975,'IUYI0909000F',1);

Answer

I understand that you want X1 values that share all their X2 values.

Here are some options. There is limitation on the number of X2 elements since their being concatenated to a single string.

select    listagg (x1,',') within group (order by x1) as x1_list

from     (select    X1
                   ,listagg (x2,',') within group (order by x2) as x2_list

          from      TEST12

          group by  X1
          )


group by  x2_list
;

X1_LIST                                                                        
--------------------------------------------------------------------------------
42858                                                                           
42974,42975       

select    X1
         ,dense_rank () over (order by listagg (x2,',') within group (order by x2)) as GROUP_ID

from      TEST12

group by  X1
;

        X1   GROUP_ID
---------- ----------
     42858          1
     42975          2
     42974          2