MySQL 8.0

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

create table t1(id int, name varchar(10))
create table t2(id int, a_id int, film varchar(100))
insert into t1(id,name) values(1,'Alpha'),
(2,'Beta'),(3,'Gamma'),
(4,'Delta')
insert into t2(id, a_id,film)
values(1,1,'Bladerunner'),
(2,1,'Star Wars'),
(3,3,'Superman'),
(4,4,'Rollerball')
select name from t1 a left join t2 b on 
a.id=b.a_id
where not exists (select 1 from t2 b1 where b.a_id=b1.a_id and film like '%S%')


name


Delta

Beta


select a.name from t1 a left join 
 t2 b on a.id=b.a_id
 where a.id  not in ( select b.a_id  from t2 b
  where 
upper(substring(b.Film,1,1))   like '%S%' and b.a_id is not null
    )


name


Delta

Beta


select b.a_id ,upper(substring(b.Film,0,1)) from t2 b
  where 
upper(substring(b.Film,1,1))   like '%S%' and b.a_id is not null


a_id
upper(substring(b.Film,0,1))


1


3