How to do SQL query to get a list of value at a certain date, from a table with start date and end date

Question

I have two tables:

Material {IDMat, Label, Model} and ReservationMat (IDRes, IDMat, StartDate, EndDate)

How can I get list of all materials that is available (not reserved) on certain date ?

Answer 1

I'm assuming your are using MySql as database engine. You could use a query like this:

select * from material where idmat in (
  select idmat from reservationmat
  where '2022-01-07' not between startdate and enddate
);

Please refer to this fiddle for the complete schema and data used to test