Unanswered: Query to identify related facilities and prevent booking clashes
I have an events database with multiple facilities registered. Some facilities belong to larger facilities, e.g. a large astroturf pitch can be sub-divided into 3 smaller pitches, the large pitch being the 'parent' and the smaller units the 'children' .
When searching for a free facility for a new booking I want to be able to identify for the same date/time period if either the parent facility is booked to prevent booking out the sub-divided units or if a sub-divided unit is booked to prevent booking out the parent facility. Can anyone suggest a simple solution to this as I haven't been able to create one as yet.
The approach I've been trying is to run a nested query to identify all bookings for a date/time period and then query the result on whether those facilities have either parent or child facilities linked to them. If so to add the related facilities and their events to the result of the inital query. Any suggestions?
In your table if you haven't add a field for children. So your table may look like:
MainID TurfName SubTurfID
In the SubturfID you would hold either the parentID for the larger turf or the childID for the smaller turf. Then when you run your query you would only check the ID in the SubTurfID to see if this is booked as well. You may have to add several childID to this field and will need a simple delimiter to enable splitting of the ID's from each other I use $ as it is unlikely to be added accidentally.