Concerts Without a Single Sold Ticket

Easy
75.

Concerts Without a Single Sold Ticket

EasyJoins

MySQL

A ticketing platform lists upcoming concerts in events. A ticket row is created when a seat is booked; if the buyer cancels, the row stays with status refunded.

Return the concerts that have no ticket with status sold — including concerts whose only tickets were refunded — with the columns event_id and event_name, in any order.

Tables

Table: events

ColumnType
event_idint
event_namevarchar
cityvarchar
event_datedate

Primary key: event_id.

One row per listed concert.

Table: tickets

ColumnTypeValues
ticket_idint
event_idint
seat_novarchar
priceint
statusenumsold, refunded

Primary key: ticket_id.

Every event_id here is in events. price is in rupees.

Example

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

events

event_idevent_namecityevent_date
1Arijit LiveMumbai2025-04-12
2Jazz by the BayMumbai2025-04-19
3NH7 WeekenderPune2025-05-03
4Coke Studio NightsDelhi2025-05-10

tickets

ticket_idevent_idseat_nopricestatus
11A122500sold
21A132500refunded
32C041200refunded
43F211800sold
52C051200refunded

Output

event_idevent_name
2Jazz by the Bay
4Coke Studio Nights
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.

Concerts Without a Single Sold Ticket — SQL Joins Problem

A ticketing platform lists upcoming concerts in events. A ticket row is created when a seat is booked; if the buyer cancels, the row stays with status…

  • Difficulty: Easy
  • Topics: Joins
  • Dialect: MySQL
  • Problem: #75

Problem statement

A ticketing platform lists upcoming concerts in events. A ticket row is created when a seat is booked; if the buyer cancels, the row stays with status refunded.

Return the concerts that have no ticket with status sold — including concerts whose only tickets were refunded — with the columns event_id and event_name, in any order.

Tables

Table: events

ColumnType
event_idint
event_namevarchar
cityvarchar
event_datedate

Primary key: event_id.

One row per listed concert.

Table: tickets

ColumnType
ticket_idint
event_idint
seat_novarchar
priceint
statusenum(sold, refunded)

Primary key: ticket_id.

Every event_id here is in events. price is in rupees.

Examples

Example 1

events

event_idevent_namecityevent_date
1Arijit LiveMumbai2025-04-12
2Jazz by the BayMumbai2025-04-19
3NH7 WeekenderPune2025-05-03
4Coke Studio NightsDelhi2025-05-10

tickets

ticket_idevent_idseat_nopricestatus
11A122500sold
21A132500refunded
32C041200refunded
43F211800sold
52C051200refunded

Output

event_idevent_name
2Jazz by the Bay
4Coke Studio Nights

How to solve Concerts Without a Single Sold Ticket

This is an anti join with a twist: only sold tickets count as a partner. The LEFT JOIN puts t.status = 'sold' in the ON clause, so a refunded ticket simply fails to match and the event survives with NULLs on the ticket side; WHERE t.ticket_id IS NULL then keeps exactly the events with no sold ticket.

Putting the status test in WHERE instead is the trap. After the join, an event whose only tickets were refunded has rows with status = 'refunded', and WHERE t.status = 'sold' AND t.ticket_id IS NULL can never hold — the event disappears, and so does every other one.

NOT EXISTS expresses the same idea without the ON/WHERE subtlety, and NOT IN is safe here because tickets.event_id is never NULL. With an index on tickets(event_id, status) every form is one probe per event.

Reference solution (MySQL)

SELECT e.event_id, e.event_name
FROM events e
LEFT JOIN tickets t ON t.event_id = e.event_id AND t.status = 'sold'
WHERE t.ticket_id IS NULL

Another way

SELECT event_id, event_name FROM events e WHERE NOT EXISTS (SELECT 1 FROM tickets t WHERE t.event_id = e.event_id AND t.status = 'sold')

Another way

SELECT event_id, event_name FROM events WHERE event_id NOT IN (SELECT event_id FROM tickets WHERE status = 'sold')

← Civic Grievances Filed per Department · NGO Donors Grouped by Email Domain →