MySQL 8.0

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

create table table1
(
   id int, 
   name varchar(255)
);
create table table2
(
   id int,
   table1_id int,
   filename varchar(255),
   filetype varchar(255)
);
insert into table1 (id,name) values
(123, 'name123'),
(124, 'name124'),
(125, 'name125'),
(126, 'name126'),
(127, 'name127'),
(128, 'name128')
insert into table2 (id,table1_id,filename,filetype) values
(1, 123, 'file1', 'cdr'),
(2, 123, 'file1', 'dxf'),
(3, 123, 'file2', 'cdr'),
(4, 123, 'file2', 'dxf'),
(5, 123, 'file3', 'dxf'),
(6, 123, 'file4', 'dxf'),
(7, 124, 'file11', 'cdr'),
(8, 124, 'file11', 'dxf'),
(9, 124, 'file12', 'cdr'),
(10, 124, 'file12', 'dxf')
select * from table1


id
name


123
name123

124
name124

125
name125

126
name126

127
name127

128
name128


select * from table2


id
table1_id
filename
filetype


1
123
file1
cdr

2
123
file1
dxf

3
123
file2
cdr

4
123
file2
dxf

5
123
file3
dxf

6
123
file4
dxf

7
124
file11
cdr

8
124
file11
dxf

9
124
file12
cdr

10
124
file12
dxf


SELECT table1.id, count(table2.filetype) as cdrCount
FROM table1
INNER JOIN table2 ON table1.id = table2.table1_id
WHERE table2.filetype = 'cdr'
GROUP BY table2.table1_id, table2.filetype
HAVING COUNT(table2.filetype) = 2


id
cdrCount


123
2

124
2


-- query doesn't work

-- expected result:
-- ----------------------------
-- | id  | cdrCount | dxfCount |
-- |-----|----------|----------|
-- | 123 |    2     |    4     |  
-- -----------------------------

SELECT table1.id, count(t21.filetype) as cdrCount, count(t22.filetype) as dxfCount
FROM table1
INNER JOIN table2 t21 ON table1.id = t21.table1_id
INNER JOIN table2 t22 ON table1.id = t22.table1_id
WHERE t21.filetype = 'cdr' AND t22.filetype = 'dxf'
GROUP BY t21.table1_id, t21.filetype, t22.table1_id, t22.filetype
HAVING COUNT(t21.filetype) = 2 AND COUNT(t22.filetype) = 4

id	name
123	name123
124	name124
125	name125
126	name126
127	name127
128	name128

id	table1_id	filename	filetype
1	123	file1	cdr
2	123	file1	dxf
3	123	file2	cdr
4	123	file2	dxf
5	123	file3	dxf
6	123	file4	dxf
7	124	file11	cdr
8	124	file11	dxf
9	124	file12	cdr
10	124	file12	dxf