MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


10.3.36-MariaDB-1:10.3.36+maria~deb10-log


create table paddocks (pd char(1) primary key, sz enum('Small', 'Big'), key (sz));
create table locks (pd char(1), lk char(2), key (pd,lk));
insert into paddocks values ('A', 'Small'), ('B', 'Small'),('C', 'Small'), ('D', 'Small'),('E', 'Small'), ('F', 'Big')

Records: 6  Duplicates: 0  Warnings: 0


insert into locks values ('A', 'KL'),('A', 'OK'),('A', 'CZ'),('A', 'CZ'),
('B', 'OK'),('B', 'OK'),
('C', 'OK'),('C', 'CZ'), ('C', 'KL'),
('D', 'RO'),('D', 'CZ'), ('D', 'CZ'),
('E', 'OK'),('E', 'OK'), ('E', 'KL'), ('E', 'KL'),
('F', 'OK'),('F', 'OK'), ('F', 'CZ'), ('F', 'KL');

Records: 20  Duplicates: 0  Warnings: 0


create temporary table ky (lk char(2), key (lk));
insert into ky values ('KL'),('OK'),('CZ'),('OK'),('CZ')

Records: 5  Duplicates: 0  Warnings: 0


select pd, locks.lk, count(locks.lk)
from paddocks
join locks using (pd)
where sz = 'Small'
group by pd, locks.lk


pd
lk
count(locks.lk)


A
CZ
2

A
KL
1

A
OK
1

B
OK
2

C
CZ
1

C
KL
1

C
OK
1

D
CZ
2

D
RO
1

E
KL
2

E
OK
2


select pd, p.lk, p.c,count(ky.lk) as kc

  from
  (select pd, locks.lk as lk, count(locks.lk) as c
from paddocks
join locks using (pd)
where sz = 'Small'
group by pd, locks.lk) p 
left join ky using (lk)
group by pd,lk


pd
lk
c
kc


A
CZ
2
2

A
KL
1
1

A
OK
1
2

B
OK
2
2

C
CZ
1
2

C
KL
1
1

C
OK
1
2

D
CZ
2
2

D
RO
1
0

E
KL
2
1

E
OK
2
2


 select pd
  from
  (
select pd, p.lk, p.c,count(ky.lk) as kc

  from
  (select pd, locks.lk as lk, count(locks.lk) as c
from paddocks
join locks using (pd)
where sz = 'Small'
group by pd, locks.lk) p 
left join ky using (lk)
group by pd,lk) p
group by pd
having count(pd) = sum(c <= kc)


pd


A

B

C


 select pd
  from
  (
select pd, p.lk, p.c,count(ky.lk) as kc

  from
  (select pd, locks.lk as lk, count(locks.lk) as c
from paddocks
join locks using (pd)
where sz = 'Small'
group by pd, locks.lk) p 
left join ky using (lk)
group by pd,lk) p
group by pd
   having sum(c) - 1 = sum(least(c,kc))


pd


D

E


select pd
  from
  (
select pd, p.lk, p.c,count(ky.lk) as kc

  from
  (select pd, locks.lk as lk, count(locks.lk) as c
from paddocks
join locks using (pd)
where sz = 'Small'
group by pd, locks.lk) p 
left join ky using (lk)
group by pd,lk) p
group by pd
having sum(c) - 1 <= sum(least(c,kc))


pd


A

B

C

D

E