Find missing records in one table excluding cancelled items
Posted on 2016-08-25
Hi, I need to find all entries in table Hotelogix which are not existing in table Reservations but excluding cancelled records in Reservations. I have tried the following statement:
SELECT HotelogixReservations.[Group Id], HotelogixReservations.[Guest Name]
FROM HotelogixReservations LEFT JOIN QueryFull ON Ltrim(Rtrim(HotelogixReservations.[Group ID])) = QueryFull.[Group Reservation No]
WHERE (((QueryFull.[Group Reservation No]) Is Null)) OR QueryFull.Cancel=TRUE;
This almost works but gives an error in the case where I have the same reservation in Reservations table twice (once as cancelled and once as an open one). Not sure how to exclude any cancelled records in reservation? Thank you for any help..