CODEWITHSUNDEEP
sqloracle-database
TRIEZEREN
TRIEZEREN

Reputation: 13

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);

Upvotes: 1

Views: 45

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

  • I understand that you want X1 values that share all their X2 values.
  • This solution is for Oracle 11g R2

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

select    count (*)                                   as x2_within_group
         ,x2_elements
         ,listagg (x1,',') within group (order by x1) as x1_list


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

          from      TEST12

          group by  X1
          )


group by  x2_list
         ,x2_elements
;

X2_WITHIN_GROUP  X2_ELEMENTS X1_LIST                      
---------------- ----------  ---------
2                23          42974,42975                   
1                6           42858    

select    dense_rank () over (order by listagg (x2,',') within group (order by x2)) as group_id
         ,X1
         ,count (*)                                                                 as x2_elements
from      TEST12

group by  X1
;

  GROUP_ID      X1      X2_ELEMENTS
---------- ----------   ------------
         1      42858      6
         2      42975      23
         2      42974      23

Upvotes: 0

Bohemian
Bohemian

Reputation: 425418

Try a join:

SELECT DISTINCT T1.X1
FROM TEST12 T1
JOIN TEST12 T2 ON T1.X2 = T2.X2
    AND T1.X1 != T2.X2

The DISTINCT is needed in case there are three or more rows that share an X2 value. If there are only ever at most two rows that share an X2 value, the DISTINCT keyword may be omitted.

Upvotes: 1

Related Questions