SQL Join Query


I’m wondering if someone can help me solve a problem with my query. I have a join query which is grabbing the time slots (only one field called timeslot) (on an outter join) and slotting my appointments table on the time to those time slots by using the following query.

FROM timeslots
(SELECT time,endtime,status,firstname,duration
FROM appointments
INNER JOIN profile
ON profile.id=appointments.id
WHERE appointments.DATE = ‘2010-11-1’) AS a
ON a.time <= timeslots.timeslot
AND a.endtime >= timeslots.timeslot;

Now the query works, but it is now duplicating because the duration, for example, (appointment starts at 09:00 and lasts for 30 minutes and ends on 09:30… The 09:00 slot would be taken, 09:15 slot would be taken and the 09:30 slot would be taken. In this case, it does that but I would like the 09:00 slot to show once, then the next slot which would be available would be 09:45

Heres an image of my query browser on what happens!

Sponsor our Newsletter | Privacy Policy | Terms of Service