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
| Column | Type |
|---|---|
| event_id | int |
| event_name | varchar |
| city | varchar |
| event_date | date |
Primary key: event_id.
One row per listed concert.
Table: tickets
| Column | Type |
|---|---|
| ticket_id | int |
| event_id | int |
| seat_no | varchar |
| price | int |
| status | enum(sold, refunded) |
Primary key: ticket_id.
Every event_id here is in events. price is in rupees.
Examples
Example 1
events
| event_id | event_name | city | event_date |
|---|---|---|---|
| 1 | Arijit Live | Mumbai | 2025-04-12 |
| 2 | Jazz by the Bay | Mumbai | 2025-04-19 |
| 3 | NH7 Weekender | Pune | 2025-05-03 |
| 4 | Coke Studio Nights | Delhi | 2025-05-10 |
tickets
| ticket_id | event_id | seat_no | price | status |
|---|---|---|---|---|
| 1 | 1 | A12 | 2500 | sold |
| 2 | 1 | A13 | 2500 | refunded |
| 3 | 2 | C04 | 1200 | refunded |
| 4 | 3 | F21 | 1800 | sold |
| 5 | 2 | C05 | 1200 | refunded |
Output
| event_id | event_name |
|---|---|
| 2 | Jazz by the Bay |
| 4 | Coke 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 NULLAnother 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 →