MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table1 (
  `ID` VARCHAR(100),
  `Val` VARCHAR(100),
  `Val2` VARCHAR(100),
  `Val3` VARCHAR(100)
);
INSERT INTO table1
  (`ID`, `Val`, `Val2`, `Val3`)
VALUES
  ('1','100','200','90'),
  ('2','100','200','10'),
  ('3','100','200','20'),
  ('19','100','200','20'),
  ('4','20','100','55'),
  ('5','20','100','10'),
  ('6','112','100','20'),
  ('7','112','100','20'),
  ('8','90','200','90'),
  ('9','30','90','180'),
  ('10','30','90','29');
select val,val2,val3
    from table1
    group by val,val2,val3
    having count(val3) > 1


val
val2
val3


100
200
20

112
100
20


select t.*
from table1 t
inner join
(
select t1.val, t1.val2
from table1 t1
 inner join
  (select val,val2,val3
    from table1
    group by val,val2,val3
    having count(val3) = 1
  ) t2
 on t1.val = t2.val and t1.val2 = t2.val2 and t1.val3 = t2.val3
 group by t1.val, t1.val2
 having count(distinct t1.id) > 1
) tmp
on tmp.val = t.val and tmp.val2 = t.val2
inner join
(select val,val2,val3
    from table1
    group by val,val2,val3
    having count(val3) = 1
  ) t3
 on t.val = t3.val and t.val2 = t3.val2 and t.val3 = t3.val3


ID
Val
Val2
Val3


1
100
200
90

2
100
200
10

4
20
100
55

5
20
100
10

9
30
90
180

10
30
90
29


select t.*
from table1 t
inner join
(
select t1.val, t1.val2
from table1 t1
 left join
  (select val,val2,val3
    from table1
    group by val,val2,val3
    having count(val3) > 1
  ) t2
 on t1.val = t2.val and t1.val2 = t2.val2 and t1.val3 = t2.val3
 where t2.val is null and t2.val2 is null and t2.val3 is null
 group by t1.val, t1.val2
 having count(distinct t1.id) > 1
) tmp
on tmp.val = t.val and tmp.val2 = t.val2


ID
Val
Val2
Val3


1
100
200
90

2
100
200
10

3
100
200
20

19
100
200
20

4
20
100
55

5
20
100
10

9
30
90
180

10
30
90
29

ID	Val	Val2	Val3
1	100	200	90
2	100	200	10
3	100	200	20
19	100	200	20
4	20	100	55
5	20	100	10
9	30	90	180
10	30	90	29

ID	Val	Val2	Val3
1	100	200	90
2	100	200	10
3	100	200	20
19	100	200	20
4	20	100	55
5	20	100	10
9	30	90	180
10	30	90	29

ID	Val	Val2	Val3
1	100	200	90
2	100	200	10
3	100	200	20
19	100	200	20
4	20	100	55
5	20	100	10
9	30	90	180
10	30	90	29