Flight Bookings by Sales Channel — SQL Joins Problem
Tickets are sold either through a partner travel agency or directly on the airline's own app. A direct booking has agency_id NULL.
- Difficulty: Easy
- Topics: Joins, Conditional Logic
- Dialect: MySQL
- Problem: #526
Problem statement
Tickets are sold either through a partner travel agency or directly on the airline's own app. A direct booking has agency_id NULL.
Return one row per booking with the columns booking_ref, passenger_name and channel, where channel is the agency's name, or the text Direct for a booking with no agency. Return the rows in any order.
Tables
Table: TravelAgency
| Column | Type |
|---|---|
| agency_id | int |
| agency_name | varchar |
| city | varchar |
Primary key: agency_id.
One row per partner agency.
Table: FlightBooking
| Column | Type |
|---|---|
| booking_ref | char |
| passenger_name | varchar |
| agency_id | int |
| fare | int |
Primary key: booking_ref.
agency_id names the agency that sold the ticket, or is NULL for a direct booking.
Examples
Example 1
TravelAgency
| agency_id | agency_name | city |
|---|---|---|
| 1 | Yatra Tours | Delhi |
| 2 | Konkan Getaways | Mumbai |
| 3 | Blue Lotus Travel | Kochi |
FlightBooking
| booking_ref | passenger_name | agency_id | fare |
|---|---|---|---|
| QX7H2P | Ananya | 1 | 6420 |
| MB91TZ | Vikram | NULL | 4890 |
| KD3W8R | Sneha | 2 | 7310 |
| PL0Q5N | Farhan | 1 | 5120 |
| ZT4E6C | Pooja | NULL | 9980 |
Output
| booking_ref | passenger_name | channel |
|---|---|---|
| QX7H2P | Ananya | Yatra Tours |
| MB91TZ | Vikram | Direct |
| KD3W8R | Sneha | Konkan Getaways |
| PL0Q5N | Farhan | Yatra Tours |
| ZT4E6C | Pooja | Direct |
How to solve Flight Bookings by Sales Channel
The answer has one row per booking, so the query starts from FlightBooking and looks up the agency. An inner join would silently drop every direct booking, because NULL = agency_id never matches; a LEFT JOIN keeps them, with NULL in every column of TravelAgency.
Those NULLs are then turned into the label the report wants. COALESCE(a.agency_name, 'Direct') returns the agency's name when there is one and Direct otherwise; IFNULL is MySQL's two-argument spelling of the same thing, and a CASE on agency_id IS NULL with a scalar subquery for the name is a third way to write it.
Since agency_id is the agency's primary key, each booking matches at most one agency and no row is duplicated. The lookup is one index probe per booking.
Reference solution (MySQL)
SELECT b.booking_ref, b.passenger_name, COALESCE(a.agency_name, 'Direct') AS channel
FROM FlightBooking b
LEFT JOIN TravelAgency a ON a.agency_id = b.agency_idAnother way
SELECT b.booking_ref, b.passenger_name,
CASE WHEN b.agency_id IS NULL THEN 'Direct'
ELSE (SELECT a.agency_name FROM TravelAgency a WHERE a.agency_id = b.agency_id) END AS channel
FROM FlightBooking bAnother way
SELECT b.booking_ref, b.passenger_name, IFNULL(a.agency_name, 'Direct') AS channel FROM TravelAgency a RIGHT JOIN FlightBooking b ON a.agency_id = b.agency_id← Flights Departing More Than an Hour Late · Current Status Labels for Train PNRs →