Current Status Labels for Train PNRs — SQL Strings Problem
A rail reservation is in exactly one of three states: confirmed (it has a coach and a berth_no), RAC (it has an rac_no) or waitlisted (it has a wl_no).
- Difficulty: Easy
- Topics: Strings, Conditional Logic
- Dialect: MySQL
- Problem: #527
Problem statement
A rail reservation is in exactly one of three states: confirmed (it has a coach and a berth_no), RAC (it has an rac_no) or waitlisted (it has a wl_no). The other state columns are NULL.
Return pnr, passenger_name and current_status, where current_status reads like the enquiry screen: CNF/<coach>/<berth_no> for a confirmed berth (for example CNF/S4/32), RAC <rac_no> (for example RAC 7) or WL <wl_no> (for example WL 23). Return the rows in any order.
Tables
Table: Reservation
| Column | Type |
|---|---|
| pnr | bigint |
| passenger_name | varchar |
| train_no | int |
| coach | varchar |
| berth_no | int |
| rac_no | int |
| wl_no | int |
Primary key: pnr.
One passenger per PNR. Exactly one of (coach and berth_no), rac_no or wl_no is filled in.
Examples
Example 1
Reservation
| pnr | passenger_name | train_no | coach | berth_no | rac_no | wl_no |
|---|---|---|---|---|---|---|
| 4521876390 | Kavya | 12627 | S4 | 32 | NULL | NULL |
| 4521876391 | Arjun | 12627 | NULL | NULL | 7 | NULL |
| 6203914875 | Neha | 12951 | NULL | NULL | NULL | 23 |
| 6203914880 | Harsh | 12951 | B2 | 5 | NULL | NULL |
| 2876104553 | Simran | 16345 | NULL | NULL | NULL | 1 |
Output
| pnr | passenger_name | current_status |
|---|---|---|
| 2876104553 | Simran | WL 1 |
| 4521876390 | Kavya | CNF/S4/32 |
| 4521876391 | Arjun | RAC 7 |
| 6203914875 | Neha | WL 23 |
| 6203914880 | Harsh | CNF/B2/5 |
How to solve Current Status Labels for Train PNRs
Each row needs a different label depending on which state columns are filled, so the natural shape is a CASE with one branch per state: a non-NULL coach means a confirmed berth, otherwise a non-NULL rac_no means RAC, and otherwise the passenger is on the waitlist. Inside each branch CONCAT builds the text, turning the numbers into strings on the way.
There is a neat trick hiding in MySQL's CONCAT rule: it returns NULL if any argument is NULL. So CONCAT('CNF/', coach, '/', berth_no) is NULL for every row that is not confirmed, CONCAT('RAC ', rac_no) is NULL unless the row is in RAC, and COALESCE over the three picks the one that exists — the first alternative.
CONCAT_WS (concat with separator) is a third spelling. Every form reads each row once; nothing is grouped or joined.
Reference solution (MySQL)
SELECT pnr, passenger_name,
CASE
WHEN coach IS NOT NULL THEN CONCAT('CNF/', coach, '/', berth_no)
WHEN rac_no IS NOT NULL THEN CONCAT('RAC ', rac_no)
ELSE CONCAT('WL ', wl_no)
END AS current_status
FROM ReservationAnother way
SELECT pnr, passenger_name, COALESCE(CONCAT('CNF/', coach, '/', berth_no), CONCAT('RAC ', rac_no), CONCAT('WL ', wl_no)) AS current_status FROM ReservationAnother way
SELECT pnr, passenger_name, IF(coach IS NULL, IF(rac_no IS NULL, CONCAT_WS(' ', 'WL', wl_no), CONCAT_WS(' ', 'RAC', rac_no)), CONCAT_WS('/', 'CNF', coach, berth_no)) AS current_status FROM Reservation← Flight Bookings by Sales Channel · Hotel Loyalty Members per Email Domain →