I'm looking to find a way to display a list of users that have cancelled a booking today.
The way my system works when a user cancels a booking is by adding a record into a cancellations table and deleting the record from the bookings table.
Currently I have
members.firstname, members.lastname, cancelations.time, cancelations.groupnumber
members inner join cancelations on members.memberid = cancelations.memberid
cancelations.date = "CURRENT_DATE"
I would go about this by changing your table structure for the
bookings table. Instead of storing the state of a cancellation across multiple tables, I would just add a new column to
isActive. This column will be set to
1 when a booking is created and will be set to
0 when one is deleted. Also when a booking is restored, it will be set to
1. This is a common technique known as "soft" deletion. It allows you to logically delete a record without actually removing it or moving it to another table. At some later point, you can archive stale deleted bookings to another table.
Here is the table structure:
CREATE TABLE bookings (`id` int PRIMARY KEY, `memberid` int, `isActive` tinyint `date` datetime);
Now the query to find out if the user does not have any bookings from today is sane and straightforward:
SELECT COUNT(*) FROM bookings WHERE memberid = 1 AND date = CURDATE() AND isActive = 1
The query given by @sagi looks promising, but when you find yourself writing complex queries to answer simple business questions it might pay to think about the architecture.