- Simple Queries (4)
- Setting criteria using WHERE (5)
- Calculations (7)
- Calculations using dates (4)
- Basic joins (8)
- More exotic joins (2)
- Aggregation and grouping (8)
- Views (5)
- Subqueries (5)
- Stored procedures (5)
- Variables (8)
- Parameters and return values (11)
- Testing conditions (1)
- Looping (3)
- Scalar functions (6)
- Transactions (5)
- Creating tables (5)
- Temporary tables and table variables (9)
- Table-valued functions (6)
- Derived tables and CTEs (13)
- Dynamic SQL (4)
- Pivots (2)
- Triggers (2)
- Archived (70)
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.
You can learn how to do this exercise if you attend the course listed below!
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:
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.
FROM [dbo].[tblCompanion] AS cm left outer JOIN [dbo].[tblEpisodeCompanion] AS Ec
ON cm.CompanionId = Ec.CompanionId
FROM [dbo].[tblCompanion] AS cm inner JOIN [dbo].[tblEpisodeCompanion] AS Ec
ON cm.CompanionId = Ec.CompanionId);
I am not getting any results. Is this the right code?
from tblCompanion as c
left outer join tblEpisodeCompanion as e
where e.EpisodeCompanionId =null
FROM [dbo].[tblCompanion] C
LEFT JOIN [dbo].[tblEpisodeCompanion] EC ON C.CompanionId = EC.CompanionId
WHERE EC.EpisodeCompanionId IS NULL OR EC.CompanionId IS NULL
[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
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