Add more columns in NOT IN MySQL?

Question

Add more columns in NOT IN MySQL?

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

0

I am new to the forum, it has helped me so much so far. But now I can not find a solution for the following:

I have a table in MySQL called "events" and another one called "vehiculos", the application I'm designing is for reserving vehicles with PHP and MySQL.

The eventos table contains: id, start_date, end_date, start_time, end_time, user, vehicle

Table vehiculos contains: id, registration, model, enabled.

When a person is going to make a reservation, the system has to assign a vehicle automatically (while it is enabled), but for this I have to know that it is not reserved for events in the range of dates and times that the user wants.

I have the following code:

SELECT vehiculos.matricula
  FROM reservas.vehiculos WHERE vehiculos.matricula NOT IN (
    SELECT vehiculo FROM reservas.eventosv
      WHERE '$aux_ini' >= CONCAT(fecha_ini,' ',hora_ini)
      OR '$aux_fin' <= CONCAT(fecha_fin,' ',hora_fin)
  ) AND habilitado='S' AND zona='Zona1' order by id asc LIMIT 1

Where $ aux_ini and $ aux_fin is start date + start time concatenated and end date + end time concatenated.

It worked correctly until the reservations of all vehicles have been made, now you can not reserve any vehicle (even if the dates are different) because it ignores the second WHERE (that of NOT IN ) since only let me select a field. Any solution?

Thanks in advance.

Greetings.

EDIT:

Doing it just like that, it returns all the license plates that are in vehicles, because there are already reservations of those vehicles on those dates, when what I'm looking for is that I return a license that is not between those dates, so there is overlap I do not understand

SET @f_ini = '2018-12-12';
SET @h_ini = '12:00';
SET @f_fin = '2018-12-14';
SET @h_fin = '17:00';

SET @ini = '2018-12-12 08:00:00';
SET @fin = '2018-12-14 08:00:00';

SELECT vehiculos.matricula
  FROM reservas.vehiculos WHERE NOT EXISTS(
    SELECT * FROM reservas.eventosv
      WHERE eventosv.vehiculo = vehiculos.matricula
        AND (   (CONCAT(@f_ini,' ',@h_ini) > @ini AND CONCAT(@f_fin,' ',@h_fin) < @fin)
             OR (CONCAT(@f_fin,' ',@h_fin) > @ini AND CONCAT(@f_ini,' ',@h_ini) < @ini)              
             OR (CONCAT(@f_fin,' ',@h_fin) > @ini AND CONCAT(@f_fin,' ',@h_fin) < @fin)
             )
  ) AND habilitado='S' AND zona='Zona1' order by id asc LIMIT 1

mysql php

asked by AdriTrake 11.12.2018 в 18:21

source

3 answers

1

I got it this way, checked

SET @ini = '2018-12-13 14:00:00';
SET @fin = '2018-12-14 12:00:00';

SELECT vehiculos.matricula
  FROM reservas.vehiculos WHERE NOT EXISTS(
    SELECT * FROM reservas.eventosv
      WHERE eventosv.vehiculo = vehiculos.matricula
        AND (  (@ini < eventosv.ffin AND @fin > eventosv.fini) OR 
                (@fin < eventosv.ffin AND @ini > eventosv.fini)
             )
  ) AND habilitado='S' AND zona='Zona1' order by vehiculos.orden asc LIMIT 1

Thanks to everyone! Especially @Fly

answered by 12.12.2018 в 18:38

0

I have managed to solve it this way

SELECT eventosv.id,vehiculos.matricula, vehiculos.orden, eventosv.fini, eventosv.ffin, vehiculos.habilitado 
FROM reservas.vehiculos LEFT JOIN reservas.eventosv 
ON vehiculos.matricula=eventosv.vehiculo 
WHERE ('2018-12-13 10:00:00' >= eventosv.ffin OR '2018-12-15 12:00:00' <= eventosv.fini) AND (vehiculos.habilitado='S') AND (vehiculos.zona='Zona1') 
GROUP BY eventosv.id 
ORDER BY eventosv.id DeSC

The problem now is that if a vehicle has been booked 2 times on different dates, if I try to request a reservation on a date that is already booked, I reassign the same vehicle because it focuses on the first dates in which it was reserved and not in the last ones.

answered by 12.12.2018 в 12:09

how to execute an action when a field is empty R: grouping the values of a dataframe in ranges

score 1 · Accepted Answer

No, you can not add more columns in NOT IN , which is going to be compared with the operator IN is a list of elements. ( link )

But anyway I think that that would not be a solution to your problem. You have possible errors in the query that could make it not work as expected.

The comparison of dates '$aux_ini' >= CONCAT(fecha_ini,' ',hora_ini) could fail depending on the format of the dates since you are comparing text strings, not dates.

To avoid this I would recommend you to work the dates in UNIX format, stored in a field INT , you have tools to treat the data both in php ( link Y link ) as in mysql ( link ) and although it's a can to turn it to visualize it you gain speed (creation of indices) and simplicity (comparisons, addition and subtraction simpler)

To get rid of doubts, verify the result of the SQL statement with real values of $aux_ini and $aux_fin (show it by screen) or also execute the tests (after emptying the current reservations) without the WHERE of the dates , I think you'll get the same re-results.

(edit)

As Miguel says, the query is simplified using NOT EXISTS but not by returning more fields (we really do not want any) but because it simplifies the search between dates.

SELECT vehiculos.matricula FROM reservas.vehicles WHERE NOT EXIST ( SELECT * FROM reservas.eventosv WHERE eventosv.vehiculos = vehiculos.matricula AND '$ aux_ini' > = CONCAT (date_ini, '', hour_ini) AND '$ aux_fin' < = CONCAT (end_date, '', end_time) ) AND enabled = 'S' AND zone = 'Zone1' order by id asc LIMIT

SELECT vehiculos.matricula
  FROM reservas.vehiculos WHERE NOT EXIST(
    SELECT * FROM reservas.eventosv
      WHERE eventosv.vehiculos = vehiculos.matricula
        AND (   (CONCAT(fecha_ini,' ',hora_ini) <= '$aux_ini' AND CONCAT(fecha_fin,' ',hora_fin) >= '$aux_fin')
             OR (CONCAT(fecha_fin,' ',hora_fin) >= '$aux_ini' AND CONCAT(fecha_ini,' ',hora_ini) <= '$aux_fin')
             OR (CONCAT(fecha_fin,' ',hora_fin) <= '$aux_fin' AND CONCAT(fecha_fin,' ',hora_fin) >= '$aux_fin')
 )
  ) AND habilitado='S' AND zona='Zona1' order by id asc LIMIT 1

What would it be: Give me the matriculas of the vehiculos for which there is NO evento of said vehiculo between the searched dates. ~~I have changed the OR of the dates by a AND , to search between the dates I want to reserve.~~ I have added the three temporary coincidences where the dates of the events with the requested reservation would overlap.