Good mates I have a problem with an SQL query in MYSQL, I explain to you how are the tables, I have a table of Events that basically are birthdays, they have among other things a start time and a Finally, I have another table of Materials that basically would be elements like Elastic Bed, Inflatable Castle, etc. These tables have a relation N to N which generates a intermediate table with both keys. In my query what I have to solve is that I show between 2 different schedules the materials that are available in those schedules , that is to say that they are not occupied by the events.
This is my query:
SELECT nombre FROM Material WHERE id NOT IN (SELECT idMaterial FROM EventoMaterial WHERE idEvento IN (SELECT id FROM Evento WHERE fecha = '".$fecha."' AND horaInicio >= '".$inicio."' AND horaFin <= '".$fin."'))
The problem here is that for example if I have an event that starts at 7:30 pm and ends at 9:30 pm and, for example, has an Elasticated Bed and a Castle assigned to it, and as a query, I ask you to show me the materials available between 19: 30 and 21:00 is going to show me that Elastic Bed and that Castle but they have to be busy in that "Time Zone" to call it in some way, because the end condition is not fulfilled. I can not find a way to say that in some way I take the condition of the beginning but the end is appropriate or something like that to the event. And tried everything and I can not find a solution.
I hope you can help me many thanks