Fantasy Teams That Forgot to Pick a Captain

Easy
451.

Fantasy Teams That Forgot to Pick a Captain

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

ColumnType
team_idint
team_namevarchar
managervarchar

Primary key: team_id.

One row per fantasy team.

Table: TeamPick

ColumnType
pick_idint
team_idint
player_namevarchar
is_captainbool

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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

FantasyTeam

team_idteam_namemanager
1Spin Doctors XIPriya Sharma
2Powerplay PiratesNikhil Rao
3Yorker KingsSneha Iyer
4Boundary RidersKaran Gupta

TeamPick

pick_idteam_idplayer_nameis_captain
11Rohan Iyer1
21Kabir Khan0
32Arjun Nair0
42Dev Mehta0
53Vikram Rao0
63Ishaan Bose1

Output

team_namemanager
Powerplay PiratesNikhil Rao
Boundary RidersKaran Gupta
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
team_idint
team_namevarchar
managervarchar

Primary key: team_id.

One row per fantasy team.

Table: TeamPick

ColumnType
pick_idint
team_idint
player_namevarchar
is_captainbool

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_idteam_namemanager
1Spin Doctors XIPriya Sharma
2Powerplay PiratesNikhil Rao
3Yorker KingsSneha Iyer
4Boundary RidersKaran Gupta

TeamPick

pick_idteam_idplayer_nameis_captain
11Rohan Iyer1
21Kabir Khan0
32Arjun Nair0
42Dev Mehta0
53Vikram Rao0
63Ishaan Bose1

Output

team_namemanager
Powerplay PiratesNikhil Rao
Boundary RidersKaran 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 NULL

Another 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 →