Z Holt Z Holt - 1 year ago 56
MySQL Question

How to get all records from two tables where doesn't exists in third

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

select distinct
members.firstname, members.lastname, cancelations.time, cancelations.groupnumber
members inner join cancelations on members.memberid = cancelations.memberid
cancelations.date = "CURRENT_DATE"

This works perfectly fine, except, this will also show if a user moves their appointment to a later/earlier time as the system will cancel then re-book.

So i believe what I would need is something like:

select distinct column names from tables where cancelations.date = "CURRENT_DATE" AND where the user hasn't got any records in the bookings table today

Tables in use (simplified)

Members - memberid, firstname, lastname
Cancelations - cancelationid, memberid, date, time, groupnumber
bookings - bookingid, memberid,date,time,groupnumber

Answer Source

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 bookings called 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:

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download