I’m trying to write an SQL query that must check if an overlap between two hours exists and if certain values are already selected or not. For example, you have a record in a table and you need to fill a form with datas. With an SQL query, you need to check if there is an overlap between hours in the table and hours that you have selected in the form but you also need to check if other datas selected in the form aren’t already used in the table compared with another data in the table. My problem is about checking if values are already in the table so I can return an error saying that those datas are already used. To be clear, you have those datas that need to be verified. You have the hours, the room, a teacher and a course. You must check if an overlap is detected with the hours (I had no problem for this part) but you also need to check if a teacher isn’t already in a certain room teaching a certain course (you can’t have a teacher in two different places), if a room isn’t already taken and also if a course isn’t already taken. I actually have this SQL query but it’s not a correct query because when I do an insert with two hours (e.g. : 08:00 and 12:00), a teacher (e.g. : teacher A), a room (e.g. : room A) and a course (e.g. : course A), there is no problem because it’s the first insertion in the table. But when I change the room (room A to room B) without changing the teacher to check if an error will be returned (because the teacher can’t be in two different places at the same time), there’s no errors and the insertion is made in the table. I’ll give you my SQL query :
select * from `reservations` where heure_debut <= '08:00' and heure_fin >= '08:00' and heure_debut <= '09:00' and heure_fin >= '09:00' and reservations.local_id = 1 and exists (select * from `reservations` where reservations.enseignant_id = 1) and exists (select * from `reservations` where reservations.Event_id = 1)
I’m trying to understand where I’m failing but I don’t see where. Thank you in advance for your answers.
Advertisement
Answer
I think you take it the wrong way, for me it’s a negative check, that will fail if it bring you more than 0 line:
- is there any courses in the same local AND at the same time ?
- OR is there any other course my teacher should give AND at the same time ?
As you can see, there is a OR I can’t find in your query.
(I don’t get you Event_id thing so I may miss a thing here)
WIP
Here is a part that can answer at least a big part of your question, tell me what still don’t work for you.
Query 1:
SET -- this is your input you try to check @local_id = 1, @heure_debut = '08:00', @heure_fin = '09:00', @enseignant_id = 1
Query 2:
-- if it return more that 0 record, then you have a conflict
SELECT
r.id AS id_line_in_conflict
, r.* -- for debug
FROM `reservations` r
WHERE
heure_debut < @heure_fin
AND heure_fin > @heure_debut
AND (
local_id = @local_id -- check if the local is empty
OR enseignant_id = @enseignant_id -- check if the teacher is free
)
| id_line_in_conflict | id | numero_semaine | date | heure_debut | heure_fin | Event_id | horaire_id | local_id | enseignant_id | |---------------------|----|----------------|----------------------|-------------|-----------|----------|------------|----------|---------------| | 1 | 1 | 16 | 2020-04-17T00:00:00Z | 08:00 | 12:00 | 1 | 4 | 1 | 1 | | 2 | 2 | 16 | 2020-04-17T00:00:00Z | 08:00 | 09:00 | 1 | 4 | 2 | 1 |
Query 3:
SET -- this is your input you try to check @local_id = 1, @heure_debut = '14:00', @heure_fin = '15:00', @enseignant_id = 3
Query 4:
-- if it return more that 0 record, then you have a conflict
SELECT
r.id AS id_line_in_conflict
, r.* -- for debug
FROM `reservations` r
WHERE
heure_debut < @heure_fin
AND heure_fin > @heure_debut
AND (
local_id = @local_id -- check if the local is empty
OR enseignant_id = @enseignant_id -- check if the teacher is free
)