Showing all appointments available query!

[Rixx]

Hello

I have a problem that is really giving me great headaches. I have had no success what so ever. My gratitude goes to anyone that can provide some insight.

I have two tables. One table for appointments and one table of all available time slots (ie 0900 is record 1, 0930 is record 2, 1000 is record 3 and so on).

If lets say there are records in the appointments table for eg today 01/06/2005 where there are two appointments (eg 0930 and 1000) I want to build a query that shows all the appointments that have not been used (so 0900, 1030, 1100,and son on). Adding to this, I need it to show these available timeslots for multiple dats (as the appointments table will have some appointments booked for the 01/06/05 and some for the 02/06/05 and some for eg 10/06/05).

I am aiming to implement this on a Active server page where the user selects the data and then the available appointments are displayed for that given date. However, I know that the query as descibed above is essential for this to be developed!

I have spent many hours trying to solve this with absolutely no success, so many thanks in advance to anyone that can help. Apologies for descibing the problem thoroughly.

Thanks

Ricky.

 

[pdx_man]

Join the two tables with a LEFT JOIN and exclude rows where there are appointments:

Select TimeSlots.TS_TheDate, TimeSlots.TS_TheTime
FROM TimeSlots
LEFT JOIN Appointments ON TimeSlots.TS_TheDate = Appointments.AP_TheDate AND TimeSlots.TS_TheTime = Appointments.AP_TheTime
WHERE Appointments.AP_TheDate IS NULL