Problem in condition query SQL MYSQL

Question

Problem in condition query SQL MYSQL

Navigation

#1 by (1 votes)

2

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

mysql sql

asked by Gaston 30.12.2017 в 00:55

source

1 answer

Is there an alternative for Raspberry pi and connect SSH under NAT? Why do I have to put two references to the GoogleMaps APIs?

score 1 · Accepted Answer

I understand the problem you are raising. But just a matter of clarifying a little additional detail, I'm going to modify your example a bit ...

Let's say you're looking to show available materials between 19:30 and 20:00 . And let's say that the trampoline is already occupied for an event of 20:00 to 21:00 . In this case, I assume that the bed should be considered available. It seems reasonable to me that there is no conflict of time at the precise minute of the 20:00 .

Taking this into account, and the example that you put in your question, the key part that you should correct is your condition with the hours (I changed the notation of your variables for readability):

AND horaInicio >= @inicio
AND horaFin <= @fin

... what should you change to:

AND horaFin > @inicio
AND horaInicio < @fin

Taking the opportunity to simplify the query, it would look like this:

select nombre
  from material
 where id not in (
   select em.idMaterial
     from Evento e
     join EventoMaterial em
       on em.idEvento = e.id
    where e.fecha = @fecha
      and e.horaFin > @inicio
      and e.horaInicio < @fin
 )

Additional note: Although the question has to do with SQL, but since you can see that you are using PHP variables, or something like that, that you concatenate directly to your SQL string, I take this opportunity to warn you that it is not good practice to do this. The correct thing is to parameterize the queries instead of concatenating the values directly in the SQL chain. By doing this you protect yourself from SQL injection and several other problems.