I have a table which stores which teacher (
teacherid) works for which group (
groupid) of pupils starting from the date
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