Details for svjmandryk
svjmandryk has participated in the following threads:Added by svjmandryk on 01 Aug 2020 at 17:04
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 :)