Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
550 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Spoiler alert :)
********************************
my solution:
create function [dbo].[getAuthorId](@authorName varchar(max))
returns varchar(max)
as
begin
return
(select DoctorId
from tblDoctor
where lower(DoctorName) like '%' + @authorName)
end
go
create function [dbo].[getEpisodesByAuthor](@authorName varchar(max))
returns table
as
return select e.EpisodeId, e.Title, e.DoctorId, ee.EnemyId, ee.EpisodeEnemyId
from tblEpisode as e
left join tblEpisodeEnemy as ee
on e.EpisodeId = ee.EpisodeId
where DoctorId = [dbo].getAuthorId(@authorName)
go
create function [dbo].[getEpisodesNotByAuthor](@authorName varchar(max))
returns table
as
return select e.EpisodeId, e.Title, e.DoctorId, ee.EnemyId, ee.EpisodeEnemyId
from tblEpisode as e
join tblEpisodeEnemy as ee
on e.EpisodeId = ee.EpisodeId
where DoctorId <> [dbo].getAuthorId(@authorName)
go
create function [dbo].[getIdsOfEpisodeWithoutExclusiveEnemies](@authorName varchar(max))
returns table
as
return
select distinct EpisodeId
from tblEpisodeEnemy
where EnemyId in (
select distinct EnemyId
from [dbo].[getEpisodesNotByAuthor](@authorName))
go
declare @authorName varchar(max) = 'Tennant'
select distinct EpisodeId, Title, DoctorId --, EnemyId, EpisodeEnemyId
from [dbo].[getEpisodesByAuthor](@authorName)
where
EpisodeId not in (
select EpisodeId
from [dbo].getIdsOfEpisodeWithoutExclusiveEnemies(@authorName))
order by Title
PS This was the trickiest one for me :)
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.