How to Retrieve Uncancelled Reservation Records from a Database?

Retrieving Uncancelled Reservation Records

Given two tables, reservation and reservation_log, where reservation contains general reservation information and reservation_log tracks changes made to reservations (including cancellations), the task is to extract reservations that have not been cancelled. A simple WHERE clause works effectively for identifying cancelled reservations, but it falls short when searching for uncancelled records.

Fortunately, two approaches can be employed to accomplish this task:

Approach 1: Using NOT IN

This method leverages the NOT IN clause to exclude reservations with matching cancellation records.

SELECT *
FROM reservation
WHERE id NOT IN (
  SELECT reservation_id
  FROM reservation_log
  WHERE change_type = 'cancel'
);

Approach 2: Using LEFT JOIN

An alternative approach is to use a LEFT JOIN operation. The left join retrieves all rows from the reservation table and includes corresponding rows from the reservation_log table if they exist. Rows without matching cancellation records will have NULL values in the reservation_log columns. The WHERE clause can then filter out these rows.

SELECT r.*
FROM reservation r
LEFT JOIN reservation_log l
  ON r.id = l.reservation_id AND l.change_type = 'cancel'
WHERE l.id IS NULL;

The above is the detailed content of How to Retrieve Uncancelled Reservation Records from a Database?. For more information, please follow other related articles on the PHP Chinese website!