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
FloridaMan   Reply to FloridaManReply
26 Sep 21 at 01:28

select *

from 

[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

waltz   Reply to waltzReply
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
except
(SELECT  cm.[CompanionId],[CompanionName]
FROM [dbo].[tblCompanion] AS cm inner JOIN [dbo].[tblEpisodeCompanion] AS Ec 
ON cm.CompanionId = Ec.CompanionId);
GO

walteragwe   Reply to walteragweReply
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
except
(SELECT  cm.[CompanionId],[CompanionName]
FROM [dbo].[tblCompanion] AS cm inner JOIN [dbo].[tblEpisodeCompanion] AS Ec 
ON cm.CompanionId = Ec.CompanionId);
GO

grao   Reply to graoReply
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

DbEnthusiast   Reply to DbEnthusiastReply
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

Phithel   Reply to PhithelReply
18 Oct 18 at 18:57

USE DoctorWho

SELECT
    C.[CompanionName],
    E.[Title]
FROM
    [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
WHERE 
    E.Title IS NULL

I got Sarah Jane Smith

linjom   Reply to linjomReply
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

Zerish   Reply to ZerishReply
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

Sevid   Reply to SevidReply
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

Add a new post