Hi, I'm working with a calendar and I have this relationship between tables
What I want is to know if the event I am entering can have the person in charge that I am choosing for him, since this person in charge may already have an event for the date that he wants to enter. The query is what I'm not sure, I know I can do a between but it's not clear to me, thank you.
With this Sql instruction you will know, if the person in charge, has an assigned event on the date that you consult. The parameters that you must provide are identifiable and the date to be consulted:
select re.nombre, ev.start, ev.end, ev.description
from events ev inner join responsable re
on ev.idresponsable = re.idresponsable
where param_idresponsable = ev.idresponsable and
param_fecha between ev.start and ev.end ;
I imagine that what you need is to know when entering an event if the responsable has already assigned another event in the date range between start and end
If so, what you could do is consult prior to the insertion of a tuple in event as follows:
Assuming that in your application at the time of this check you have 3 variables with the values to consult
param_start start date of the period of the new event to be inserted
param_end end date of the period of the new event to be inserted '
param_responsable identifier of the person responsible for whom you want to insert a new event
select 1 from event where idresponsable = param_responsable and ((start between param_start and param_end) or (end between param_start and param_end))
In this way, if this query yields results, it means that the person in charge has an event such that some of the start or end dates of the new event to be inserted fall within the range of the previous event, which would make its presence impossible.