Reservation System - Semi-Complicated SELECT Statement

[hoopplaya4]

Hello all,

 

I currently have 3 tables, which I'm using for people to make reservations for certain pieces of equipment. 

 

Here are my tables:

 

    tblEquipment:

        id        name        description

        1      Camera        Takes pictures

        2      Projector      Projects pictures

        3      Laptop        Portable Computer

 

 

    tblEvents:

        id        start                end              first_name        last_name              email

        1    2009-08-10      2009-08-11          John                Doe                [email protected]

        2    2009-08-15      2009-08-16          Jane                Doe                [email protected]

 

 

    tblEventData:

        id        eventID            equipmentID

        1              1                        1             

        2              1                        2

 

 

Right now, a user will submit a query with their requested times, then they will see all available equipment.

 

So, using the exampe above, if a user is looking for equipment between 8/10-8/11, he will see that the only equipment that is available is: equipmentID 3 (Laptop).

 

How can I create my query to return only the available equipment based on the requested times?

 

This is what I've come up with so far, but can't get it to work:

 

SELECT tblequipment.id as name, tblEvents.start as start, tblEvents.end as end

FROM tblEquipment

INNER JOIN tblEventData on tblEventData.equipmentID = tblEquipment.id

INNER JOIN tblEvents on tbleventdata.eventID = tblEvents.id

WHERE NOT EXISTS(SELECT * FROM tblEvents WHERE $end >= start AND $start <= end)

 

 

Any ideas?  Thanks!

[kickstart]

Hi

 

One issue with your current select is that it appears it will only catch events that are totally within the same data range, while those that just overlap will be ignored. You only need to check if either the end or start are within the data range.

 

However, try something like this, putting the date check on the ON statement.

 

SELECT tblequipment.id as name

FROM tblEquipment

INNER JOIN tblEventData ON tblEventData.equipmentID = tblEquipment.id

LEFT OUTER JOIN tblEvents ON tbleventdata.eventID = tblEvents.id AND (($end >= start AND $end <= end) OR ($start >= start AND $start <= end))

WHERE tblEvents.id IS NULL

 

All the best

 

Keith