# Mysql – Finding rows for a specified date range

datetimeMySQL

I have a table which stores which teacher (teacherid) works for which group (groupid) of pupils starting from the date startdate:

CREATE TABLE pupilgroupteacher (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
groupid smallint(5) unsigned NOT NULL,
startdate date NOT NULL,
teacherid int(10) unsigned DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY date (groupid,startdate),
KEY teacher (teacherid),
KEY group (groupid),
CONSTRAINT fk_pupilgroupteacher_2 FOREIGN KEY (groupid) REFERENCES pupilgroup (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT fk_pupilgroupteacher_1 FOREIGN KEY (teacherid) REFERENCES employee (personid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Having a teacher ID and a month, I need to find all groups for which this teacher worked (or is going to work) at least one day in the given month.

Note: For a given teacherid there may be more than one startdate (if, for example, a teacher worked for a group, was replaced with another teacher and then replaced back on a later startdate).

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher
AND startdate >= @month + INTERVAL 1 DAY
AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM
( SELECT DISTINCT groupid
FROM pupilgroupteacher
) AS gd
JOIN pupilgroupteacher AS gg
ON  gg.groupid = gd.groupid
AND gg.startdate =
( SELECT MAX(gi.startdate)
FROM pupilgroupteacher AS gi
WHERE gi.groupid = gd.groupid
AND gi.startdate < @month + INTERVAL 1 DAY
)
WHERE gg.teacherid = @teacher ;