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','aa'),
  ('2','100','200','aa'),
  ('3','100','200','aa'),
  ('19','100','200','ab'),
  ('4','20','100','SD2'),
  ('5','20','100','SD1'),
  ('6','112','100','aa'),
  ('7','112','100','ab'),
  ('8','90','200','aa'),
  ('9','30','90','SF2'),
  ('10','30','90','SF1');
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


4
20
100
SD2

5
20
100
SD1

9
30
90
SF2

10
30
90
SF1


select t1.*
from table1 t1
where 
    (
        select count(*) 
        from table1 t2 
        where t2.val = t1.val and t2.val2 = t1.val2
    ) > 1
    and (
        select count(*)
        from table1 t2
        where t2.val = t1.val and t2.val2 = t1.val2 and t2.val3 = t1.val3
    ) = 1
order by id


ID
Val
Val2
Val3


10
30
90
SF1

19
100
200
ab

4
20
100
SD2

5
20
100
SD1

6
112
100
aa

7
112
100
ab

9
30
90
SF2

ID	Val	Val2	Val3
10	30	90	SF1
19	100	200	ab
4	20	100	SD2
5	20	100	SD1
6	112	100	aa
7	112	100	ab
9	30	90	SF2

ID	Val	Val2	Val3
10	30	90	SF1
19	100	200	ab
4	20	100	SD2
5	20	100	SD1
6	112	100	aa
7	112	100	ab
9	30	90	SF2

ID	Val	Val2	Val3
10	30	90	SF1
19	100	200	ab
4	20	100	SD2
5	20	100	SD1
6	112	100	aa
7	112	100	ab
9	30	90	SF2