Return row only if value doesn't exist
mysql, sql
Solution
SELECT *
FROM reservation
WHERE id NOT IN (select reservation_id
FROM reservation_log
WHERE change_type = 'cancel')
OR:
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 first version is more intuitive, but I think the second version usually gets better performance (assuming you have indexes on the columns used in the join).
The second version works because `LEFT JOIN` returns a row for all rows in the first table. When the `ON` condition succeeds, those rows will include the columns from the second table, just like `INNER JOIN`. When the condition fails, the returned row will contain `NULL` for all the columns in the second table. The `WHERE l.id IS NULL` test then matches those rows, so it finds all the rows that don't have a match between the tables.
Problem
I have 2 tables - `reservation`: ``` id | some_other_column ----+------------------ 1 | value 2 | value 3 | value ``` And second table - `reservation_log`: ``` id | reservation_id | change_type ----+----------------+------------- 1 | 1 | create 2 | 2 | create 3 | 3 | create 4 | 1 | cancel 5 | 2 | cancel ``` I need to select only reservations NOT cancelled (it is only ID 3 in this example). I can easily select cancelled with a simple `WHERE change_type = cancel` condition, but I'm struggling with NOT cancelled, since the simple `WHERE` doesn't work here.