Our training courses

Other training resources

Our training venues

Why we are different

Details for Marcus

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

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.

End of small page here
Please be aware that our website uses cookies!
I'm OK with this Tell me more ...