SQL | More exotic joins exercise | Use outer joins to show companions who have no episodes

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Software ==> SQL  (198 exercises)
Version ==> Any version of SQL Server
Topic ==> More exotic joins  (2 exercises)
Level ==> Average difficulty
Subject ==> SQL training
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

If you haven't already done so, run the script in the above folder to generate the Doctor Who database.

The database contains details of companions, and the episodes each appeared in:

Episodes and companions

Each companion can appear in one or more episodes.

Use this to list the names of the companions who haven't featured in any episodes.  You should find there's one of them, but we won't spoil the surprise by saying who it is!

Create a query based on the companions table, with an outer join to the episode companion table.

Save this query as outre.sql (that's neither a misspelling nor a funny joke), and close it down.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.
This page has 4 threads Add post
26 Sep 21 at 01:28

select *


[dbo].[tblEpisode] e

left join [dbo].[tblEpisodeCompanion] ec

on e.EpisodeId = ec.EpisodeId

--where e.EpisodeId is null

full outer join [dbo].[tblCompanion] c

on ec.CompanionId = c.CompanionId

09 May 21 at 11:23

SELECT  cm.[CompanionId],[CompanionName]
FROM [dbo].[tblCompanion] AS cm left outer JOIN [dbo].[tblEpisodeCompanion] AS Ec
ON cm.CompanionId = Ec.CompanionId
(SELECT  cm.[CompanionId],[CompanionName]
FROM [dbo].[tblCompanion] AS cm inner JOIN [dbo].[tblEpisodeCompanion] AS Ec 
ON cm.CompanionId = Ec.CompanionId);

20 Jan 21 at 03:43

SELECT  cm.[CompanionId],[CompanionName]
FROM [dbo].[tblCompanion] AS cm left outer JOIN [dbo].[tblEpisodeCompanion] AS Ec
ON cm.CompanionId = Ec.CompanionId
(SELECT  cm.[CompanionId],[CompanionName]
FROM [dbo].[tblCompanion] AS cm inner JOIN [dbo].[tblEpisodeCompanion] AS Ec 
ON cm.CompanionId = Ec.CompanionId);

07 Nov 17 at 01:34

I am not getting any results. Is this the right code?

use doctorwho

Select companionname
from tblCompanion as c
left outer join tblEpisodeCompanion as e
on c.CompanionId=e.EpisodeCompanionId
where e.EpisodeCompanionId =null

19 Dec 20 at 16:11

SELECT C.CompanionName,C.WhoPlayed,EC.*
FROM [dbo].[tblCompanion] C
LEFT JOIN [dbo].[tblEpisodeCompanion] EC ON C.CompanionId = EC.CompanionId
WHERE EC.EpisodeCompanionId IS NULL OR EC.CompanionId IS NULL

18 Oct 18 at 18:57

USE DoctorWho

    [dbo].[tblCompanion] AS C
        LEFT OUTER JOIN [dbo].[tblEpisodeCompanion] AS EC
            ON C.CompanionId = EC.CompanionId
        LEFT OUTER JOIN [dbo].[tblEpisode] AS E
            ON EC.EpisodeId = E.EpisodeId
    E.Title IS NULL

I got Sarah Jane Smith

19 Apr 18 at 07:21

select c.CompanionName,c.WhoPlayed,E.Episodeid,E.EpisodeCompanionId 
from tblCompanion as C 
     left  join   tblEpisodeCompanion as E
    on c.CompanionId = E.CompanionId
    where (E.EpisodeCompanionId is null or E.EpisodeId is null)

Result i got      Sarah Jane Smith    Elisabeth Sladen    NULL    NULL

16 May 18 at 07:33

Right join will do just fine

SELECT [CompanionName]
FROM [dbo].[tblEpisodeCompanion]
RIGHT  JOIN [dbo].[tblCompanion]
ON tblEpisodeCompanion.CompanionId=tblCompanion.CompanionId
WHERE EpisodeCompanionId IS NULL

23 Mar 18 at 00:50

You must use "where e.CompanionId is null", this is the result that I got :

CompanionName    CompanionId    WhoPlayed
Sarah Jane Smith    8                       Elisabeth Sladen