I’m not very experienced with database architecture, so maybe I’ve dug myself a bit of a hole here. I have three tables:
- “courseTypes”, a listing of 8 different courses that people can do.
- “courseDates”, a listing of all the (many) dates these courses are held between (startDate and finishDate for each)
- “types_dates”, a lookup table kind of thing (not sure if that’s the right thing to call it) that assigns a type ID to every date ID
For all of my SELECT statements, I’m using INNER JOIN to get data from all three tables, but I don’t know how to do that for this next bit.
What I want to do is check whether today’s date is later than the startDate for the last set of dates for each course. In other words, if the last set of dates for a course type has already started, you can’t join it any more.
I have a vague idea of doing max(startDate), but I’m not sure how to go on from there. In pseudocode it would be something along the lines of “SELECT max(startDate) FROM courseDates WHERE this is the max(startDate) of only this courseType.id” . . . I’m doing kind of OK until we get to the “WHERE” clause
Note that what I want to return is an array of the last dates of each course type, obviously with some kind of identifier so I can tell which is which. So hardcoding for the courseType id won’t work.
Any suggestions are appreciated! As I mentioned, I’m not great at databases so if the answer is “If you’d set up your tables differently, you wouldn’t have this problem”, please let me know.