Skip to content
Advertisement

Checking for an overlap and if values are already taken

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.

SQL Fiddle

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

Results:

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
    )

Results:

| 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

Results:

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
    )

Results:

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement