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 * from table1


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

6
112
100
20

7
112
100
20

8
90
200
90

9
30
90
180

10
30
90
29


SELECT
	t1.* 
FROM
	table1 t1 
WHERE
	EXISTS (
	SELECT
		1 
	FROM
		table1 
	WHERE
		ID <> t1.ID 
		AND Val = t1.Val 
		AND Val2 = t1.Val2 
	) 
	AND NOT EXISTS (
	SELECT
		1 
	FROM
		table1 
	WHERE
		Val = t1.Val 
		AND Val2 = t1.Val2 
	AND Val3 IN ( 
	SELECT Val3 
	FROM table1 
	GROUP BY Val3 
	HAVING count( * ) > 1 
	) 
)


ID
Val
Val2
Val3


9
30
90
180

10
30
90
29


SELECT a.*
  FROM table1 a
  JOIN 
     ( SELECT val
            , val2 
         FROM table1 
        GROUP 
           BY val
            , val2 
       HAVING COUNT(*) > 1 
          AND MIN(val3) <> MAX(val3)
     ) b
    ON b.val = a.val
   AND b.val2 = a.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
6	112	100	20
7	112	100	20
8	90	200	90
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
6	112	100	20
7	112	100	20
8	90	200	90
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
6	112	100	20
7	112	100	20
8	90	200	90
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