I am trying to make a query to do something, but am not sure how to go about it, i.e should be aiming to JOIN tables, if so how (I hate JOINing tables, never knowing what way round to use things (which if anybody has a link to anything explaining this that’d be great for me to read to try and have a go myself!)), or should it be another way, or should I re-design my database.
Basically, I’m building an application in a school environment, so each school has a ‘year’ and a ‘year’ can have multiple ‘terms’. Currently my database is designed thus:
YEARS(yearID, yearName)
TERMS(termID, yearID, termName, termStart, termEnd, termHalfTerm)
What I want to do is do a query which can: SELECT every year where either a) no terms belong to it, or b) the last term (ordered by term end, and each term cannot overlap) term end date is after today (in other words, we are somewhere in that year).
I then wish to get the termStart date of the first term in the year, ordered by term starting date, so that the user has: {Year Name} ({Term Start} - {Term End}) as an eventual output. An years which are out of date we can safely ignore.
So, should I remake the years table with a yearStart and yearEnd which I populate based on termStart and termEnd in it’s year and update them when this changed (although this seemed clumsy, keeps data around that might grow old and just seemed like some bad database design if I could do it another way). Many thanks for any help.