I need a light. I am creating a car travel scheduling system and I need to prior to including a new trip, which is checked if the start date and time of the trip and the end date and time of the trip have not been occupied, using that vehicle that is like FK and this driver who is like FK.
In other words, before the trip is included, know that the car E is the driver and the date and time are available.
I'll assume that :data_inicio and :data_fim are the timestamps of the trip you want to tag, with the driver% id and vehicle :idm .
I will also assume that your tables are :idv , viagem and motorista , viatura and motorista have a viatura field in each being primary key and id has the fields viagem and id_motorista . In the id_veiculo table we also have the viagem and inicio columns, which are timestamps. All of these fields are fim .
SELECT g.*
FROM viagem g
INNER JOIN motorista m ON g.id_motorista = m.id
INNER JOIN viatura v ON g.id_viatura = v.id
WHERE (m.id = :idm OR v.id = :idv)
AND NOT (g.inicio > :data_fim)
AND NOT (g.fim < :data_inicio)
This query will result in the trips that are colliding with the one you want to register. It works by looking for travel with:
The same driver OR the same vehicle.
That do not start after the date / time you finish the trip you want to register
These last two conditions are the most important part because if the trip you want to register ends before you start the already registered or starts after you finish the already registered, then it does not collide. Otherwise, there was certainly a time collision.