Flight Bookings by Sales Channel

Easy
526.

Flight Bookings by Sales Channel

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

ColumnType
agency_idint
agency_namevarchar
cityvarchar

Primary key: agency_id.

One row per partner agency.

Table: FlightBooking

ColumnType
booking_refchar
passenger_namevarchar
agency_idint
fareint

Primary key: booking_ref.

agency_id names the agency that sold the ticket, or is NULL for a direct booking.

Example

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

TravelAgency

agency_idagency_namecity
1Yatra ToursDelhi
2Konkan GetawaysMumbai
3Blue Lotus TravelKochi

FlightBooking

booking_refpassenger_nameagency_idfare
QX7H2PAnanya16420
MB91TZVikramNULL4890
KD3W8RSneha27310
PL0Q5NFarhan15120
ZT4E6CPoojaNULL9980

Output

booking_refpassenger_namechannel
QX7H2PAnanyaYatra Tours
MB91TZVikramDirect
KD3W8RSnehaKonkan Getaways
PL0Q5NFarhanYatra Tours
ZT4E6CPoojaDirect
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.

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

ColumnType
agency_idint
agency_namevarchar
cityvarchar

Primary key: agency_id.

One row per partner agency.

Table: FlightBooking

ColumnType
booking_refchar
passenger_namevarchar
agency_idint
fareint

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_idagency_namecity
1Yatra ToursDelhi
2Konkan GetawaysMumbai
3Blue Lotus TravelKochi

FlightBooking

booking_refpassenger_nameagency_idfare
QX7H2PAnanya16420
MB91TZVikramNULL4890
KD3W8RSneha27310
PL0Q5NFarhan15120
ZT4E6CPoojaNULL9980

Output

booking_refpassenger_namechannel
QX7H2PAnanyaYatra Tours
MB91TZVikramDirect
KD3W8RSnehaKonkan Getaways
PL0Q5NFarhanYatra Tours
ZT4E6CPoojaDirect

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_id

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

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