Sorry for the length of detail required to ask the question.
There are four tables (related to research, not really having anything to do with a sporting facility). They're as follows:
1) Let's say the first table is a list of tennis courts, and let's say there are hundreds of possibilities (not just indoor and outdoor).
-------------
TENNIS_COURTS
ID Type
-------------
1 Indoor
2 Outdoor
…
2) We want to note which day of the year they're available for rental. To prevent redundant rows, we can list individual days (e.g., just the 2nd day of the year, entered as "From:2", "To:2") or blocks (e.g., from the 24th day through the 25th day, entered as "From:24", "To:25"). In this example, the indoor court is the most available while the outdoor court has only two date ranges (obviously unrealistic for winter).
---------------------------
DAYS_AVAILABLE
ID ProductID From To
---------------------------
1 1 2 2 《 Indoor
2 2 24 25 《 Outdoor
3 2 140 170 《 Outdoor
4 1 280 300 《 Indoor
5 1 340 345 《 Indoor
…
3) We also want to add a list of attributes which will grow quite long over time. So rather than incorporating these in a field rule, there's an Attributes table.
-----------------------
ATTRIBUTES
ID Attribute
-----------------------
1 Age of Player
2 Time of Day
3 Outside Temperature
…
4) Lastly, we want to add a list of Considerations (or factors) to consider when renting a court. In this example, risk of injury applies to both indoor and outdoor courts, but visibility and temperature only applies to outdoor.
--------------------------------------------------
CONSIDERATIONS
ID ProductID AttributeID Effect Link
--------------------------------------------------
1 1 1 Risk of injury www… 《 Indoor
2 2 1 Risk of injury www… 《 Outdoor
3 2 2 Hard to see www… 《 Outdoor
4 2 3 Gets cold www… 《 Outdoor
…
Utilizing the individual tables above, we'd like to create a consolidated saved view that contains at least one row for each date in the range, starting from the first day of the year (in which a court is available) through the last day of the year (for which a court is available). We also want to repeat the applicable considerations for each day listed.
Based on the data shown above, it would look like this:
----------------------------------------
CONSOLIDATED VIEW
Day Court Consideration Link
----------------------------------------
2 Indoor 《 from DAYS_AVAILABLE
2 Indoor Risk of injury www… 《 from CONSIDERATIONS
24 Outdoor 《 from DAYS_AVAILABLE
24 Outdoor Risk of injury www… 《 from CONSIDERATIONS
24 Outdoor Hard to see www… 《 from CONSIDERATIONS
24 Outdoor Gets cold www… 《 from CONSIDERATIONS
25 Outdoor 《 from DAYS_AVAILABLE
25 Outdoor Risk of injury www… 《 from CONSIDERATIONS
25 Outdoor Hard to see www… 《 from CONSIDERATIONS
25 Outdoor Gets cold www… 《 from CONSIDERATIONS
…
We can then query the consolidated view (e.g., "SELECT * FROM CONSOLIDATED_VIEW where Day = 24") to produce a simple output like:
Court: Indoor
Available: 24th day
Note: Risk of injury (www…)
Hard to see (www…)
Gets cold (www…)
We want to produce the above shown example from a consolidated view because once the data is stored, it won't be changing frequently, and we very likely will not be querying single days at a time anyhow. It's more likely that a web client will fetch all of the rows into a large array (TBD based on determining the total size), and will then present it to users without further server interaction.
Can we produce the CONSLIDATED_TABLE solely with an SQL query or do we need to perform some other coding (e.g., PHP or NodeJS)?
The real deal in your question is: how can I get a list of the available days so I can join my other tables and produce my output, right? I mean, having a list of days, all you need to is JOIN the other tables.
As you have a limited list (days of the year), I'd suggest creating a table with a single column containing the 365 (or 366) days (1, 2, 3, ...) and JOIN it with your other tables. The query would be smtg similar to:
SELECT ... -- fields u want
FROM YOUR_NEW_TABLE n
JOIN DAYS_AVAILABLE D on (n.DAY between D.From and D.To)
JOIN ... -- other tables that you need info