SELECT max(DATE) with INNER JOIN

Hi all,

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 :stuck_out_tongue:

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.

Thanks everyone!

hey ricki, i’m a little unclear about your tables

if you’re using mysql, could you do a SHOW CREATE TABLE for each one

also pls show a few rows of sample data for each table

i’m a little concerned about dates having an id – usually dates serve as their own id

oh, and could you repeat the question you’re having trouble with?

Hey, thanks for your reply. I’m out of the office all day so I’m not able to do the SHOW CREATE TABLE bit until tomorrow.

The dates have an ID because there is actually overlap with some of the dates - there is no way to guarantee that either startDate or finishDate would be unique. So I have also got this auto-incrementing id column, which serves as primary key.

The question I will also further clarify when I have my tables, so it will hopefully be clear.

I’m going to assume that your structure & data looks something like this:

courseType
id	name
1	accounting
2	hairdressing
3	welding

courseDates
id	courseTypeId	startDate	endDate
1	1		2014-05-01	2014-07-01
2	1		2014-06-01	2014-08-01
3	2		2014-07-01	2014-07-15
4	2		2014-07-20	2014-08-01
5	2		2014-09-01	2014-09-14

Although I’m not sure what the proposed types_dates table was for.

RE: Rudy’s comment about courseDates.id being redundant - he’s saying to use courseTypeId and startDate as a combined primary key for the table. It doesn’t matter if the dates overlap (mine do), as long as a course doesn’t have two entries starting on the same day, then courseTypeId_startDate would be unique, the the auto_increment is unnecessary. If you can have two instances of a course starting on the same date, then you’ll need the auto_increment.

For the query, I’ve listed all courses, and their most recent start date, if they’ve got one (if not this will be NULL).


SELECT
courseType.id as courseTypeId, courseType.name as courseTypeName,
MAX(startDate) as lastStartDate
FROM
courseType
LEFT JOIN courseDates ON courseType.id = courseDates.courseTypeId
GROUP BY courseType.id

Result:


courseTypeId	courseTypeName	lastStartDate
1		accounting	2014-06-01
2		hairdressing	2014-09-01
3		welding		NULL

Note that courseType 3 has no date entries so has a NULL. If you didn’t want to show courses with no starts, change the LEFT JOIN to JOIN.
If you wanted to only return courses whose most recent start date is today or later, just add a HAVING clause after the GROUP BY clause, like so:


SELECT
courseType.id as courseTypeId, courseType.name as courseTypeName,
MAX(startDate) as lastStartDate
FROM
courseType
LEFT JOIN courseDates ON courseType.id = courseDates.courseTypeId
GROUP BY courseType.id
HAVING lastStartDate>=CURDATE()

Result:


courseTypeId	courseTypeName	lastStartDate
2		hairdressing	2014-09-01

Is this what you wanted?

if it isn’t, it should be

:slight_smile:

I haven’t had a chance to try and apply this yet, but I very much suspect it will be. I will give it a go and report back.

The reason I have the “types_dates” table would be a function of my inexperience with databases - I’ve never even heard the phrase “combined primary key” before :stuck_out_tongue: