How do you query based on conditions that apply to root table and its referenced tables
I am trying to create the database to allow for rooms rentals. People would be able to post the room availability and others would be able to book it off.
The way I am intending to do this to get around the no-joins (and I am up for suggestions) is as follows:
A Room table will have have the room details. In addition it will have 2 columns (Available times, booked times). Column 1 will reference another table (1:N relation) that has all the availabilities (say Jan1st at 1pm till Jan 10th at 10 pm, Feb 2nd at 8am till Feb 5th at 9pm and so on). Also it will have column 2 which reference another table that has all the occupied date/timeslots like the format before). The Room table has a GEO column offcourse
My question is, is the a way to do a search with condition on the Room table and the 2 related tables.
Select all the rooms that are within 10km from my location where the current date is within column 1 dates AND current date is NOT within column 2 dates.
If so do you have a sample syntax for how to condition the query on the Room table and its referenced tables
From the documentation, I was able to understand how to do condition on a table but not on its related tables
I hope my question makes sense. Again I am open for a different schema but thats the best I can think of