Find Common Values in VIew
<p>using oracle database 12
I have this data </p>

<pre><code>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
</code></pre>

<p>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</p>

<p>I use this query but it doesn't return anything</p>

<pre><code>SELECT 
TT.X1
  FROM TEST12 TT
group by TT.WORK_ORDER 
having count(distinct TT.X2) = (select count (distinct tt2.X2) from TEST12 tt2)
</code></pre>

<p>but if I removed 42858 from the data, it returns 42974 and 42975</p>

<p>Please advise</p>

<p>thanks,
Journey Man</p>

<pre><code>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);
</code></pre>