Fantasy Teams That Forgot to Pick a Captain — SQL Problem
Before the gameweek locks, the fantasy app emails every manager whose team has no captain among its picks.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #451
Problem statement
Before the gameweek locks, the fantasy app emails every manager whose team has no captain among its picks. A team that has not picked any players yet has no captain either.
Return the columns team_name and manager for every such team. Return the rows in any order.
Tables
Table: FantasyTeam
| Column | Type |
|---|---|
| team_id | int |
| team_name | varchar |
| manager | varchar |
Primary key: team_id.
One row per fantasy team.
Table: TeamPick
| Column | Type |
|---|---|
| pick_id | int |
| team_id | int |
| player_name | varchar |
| is_captain | bool |
Primary key: pick_id.
One row per player picked into a team for this gameweek; is_captain is 1 for the captain. team_id is always in FantasyTeam.
Examples
Example 1
FantasyTeam
| team_id | team_name | manager |
|---|---|---|
| 1 | Spin Doctors XI | Priya Sharma |
| 2 | Powerplay Pirates | Nikhil Rao |
| 3 | Yorker Kings | Sneha Iyer |
| 4 | Boundary Riders | Karan Gupta |
TeamPick
| pick_id | team_id | player_name | is_captain |
|---|---|---|---|
| 1 | 1 | Rohan Iyer | 1 |
| 2 | 1 | Kabir Khan | 0 |
| 3 | 2 | Arjun Nair | 0 |
| 4 | 2 | Dev Mehta | 0 |
| 5 | 3 | Vikram Rao | 0 |
| 6 | 3 | Ishaan Bose | 1 |
Output
| team_name | manager |
|---|---|
| Powerplay Pirates | Nikhil Rao |
| Boundary Riders | Karan Gupta |
How to solve Fantasy Teams That Forgot to Pick a Captain
The answer is the teams for which a certain row does not exist — a captain pick — which is an anti join. NOT EXISTS says it directly: for each team, look for a pick of that team with is_captain = 1, and keep the team if there is none. A team with no picks at all has nothing to find, so it is kept too, as the statement asks.
The LEFT JOIN form needs care with where the captain condition goes. In the ON clause it means "join only the captain pick", so a team without one gets a single row of NULLs, which p.pick_id IS NULL keeps. Moved to WHERE, the condition would run after the join and remove the NULL rows — leaving only teams that do have a captain, the opposite of the question.
NOT IN works here because team_id is never NULL in TeamPick. Each form is one lookup per team with an index on TeamPick(team_id).
Reference solution (MySQL)
SELECT t.team_name, t.manager
FROM FantasyTeam t
WHERE NOT EXISTS (
SELECT 1 FROM TeamPick p
WHERE p.team_id = t.team_id AND p.is_captain = 1
)Another way
SELECT t.team_name, t.manager FROM FantasyTeam t LEFT JOIN TeamPick p ON p.team_id = t.team_id AND p.is_captain = 1 WHERE p.pick_id IS NULLAnother way
SELECT team_name, manager FROM FantasyTeam WHERE team_id NOT IN (SELECT team_id FROM TeamPick WHERE is_captain = 1)← Full-Time Results Board for the Football League · Overseas Players in the Auction Pool by Role →