WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 520 reviews for our classroom and online training

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 :)