MySQL 8.0

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

CREATE TABLE `matcher_table` AS (SELECT 1 id, 'MATCH1' matcher, 'TYPE1' type
                    UNION ALL
                    SELECT 2, 'MATCH1', 'TYPE2'
                    UNION ALL
                    SELECT 3, 'MATCH1', 'TYPE3'
                    UNION ALL
                    SELECT 4, 'MATCH2', 'TYPE4'
                    UNION ALL
                    SELECT 5, 'MATCH2', 'TYPE2'
                    UNION ALL
                    SELECT 6, 'MATCH3', 'TYPE1'
                    UNION ALL
                    SELECT 7, 'MATCH3', 'TYPE2'
                    UNION ALL
                    SELECT 8, 'MATCH3', 'TYPE3');

Records: 8  Duplicates: 0  Warnings: 0


SELECT * FROM `matcher_table`


id
matcher
type


1
MATCH1
TYPE1

2
MATCH1
TYPE2

3
MATCH1
TYPE3

4
MATCH2
TYPE4

5
MATCH2
TYPE2

6
MATCH3
TYPE1

7
MATCH3
TYPE2

8
MATCH3
TYPE3


select * from `matcher_table` where matcher in (select matcher
from `matcher_table`
where lower(type) in ('type1', 'type2', 'type3')
group by matcher
having count(distinct type) = 3)


id
matcher
type


1
MATCH1
TYPE1

2
MATCH1
TYPE2

3
MATCH1
TYPE3

6
MATCH3
TYPE1

7
MATCH3
TYPE2

8
MATCH3
TYPE3

id	matcher	type
1	MATCH1	TYPE1
2	MATCH1	TYPE2
3	MATCH1	TYPE3
4	MATCH2	TYPE4
5	MATCH2	TYPE2
6	MATCH3	TYPE1
7	MATCH3	TYPE2
8	MATCH3	TYPE3