Semi-sophisticated PHP / MySQL query

Question

Currently, I have 3 tables that I use for people to make reservations for specific 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                 [e-mail]
     2     2009-08-15      2009-08-16          Jane                 Doe                 [e-mail]


tblEventData:
     id         eventID             equipmentID
     1              1                         1             
     2              1                         2

At the moment, the user will send a request with the requested time, then they will see all the available equipment.

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

How can I create my request to return only available equipment depending on the requested times?

This is what I came up with, but can't make it 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!

Answer 1

, , NOT EXISTS, , . , " , ". , . : " , , ".

- :

SELECT tblequipment.id as name
FROM tblEquipment
WHERE NOT EXISTS 
  (SELECT * FROM tblEvents 
   INNER JOIN tblEventData ON (tblEvents.id = tblEventData.eventID)
   WHERE $end >= start AND $start <= end
   AND tblEventData.equipmentID = tblEquipment.id)

EDIT: JOIN , , , - , , .

, , NOT EXISTS, .

Answer 2

,

`end`

, mysql , .

Answer 3

"end" - SQL. tblEvents.end - .