Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
add comment

2 Answers

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 ;
share|improve this answer
add comment

It seems that the following code solves my problem:

SELECT DISTINCT groupid
FROM pupilgroupteacher x
WHERE teacherid=@teacher AND startdate<@month+INTERVAL 1 MONTH AND
NOT EXISTS(
SELECT *
FROM pupilgroupteacher
WHERE groupid=x.groupid AND teacherid!=@teacher AND startdate>x.startdate AND startdate<@month
)

(Here @month is the date of the first day of the specified month and @teacher is the teacher ID.)

But:

  • is this code error-free? Please help to check it.
  • can it be optimized to use fewer server resources?
share|improve this answer
1  
I think it's correct but you (may) need to replace the AND startdate<@month) with AND startdate <= @month) (if as I assume, when a teacher starts to work at 1st of July, then the previous teacher counts as having taught in June only.) –  ypercube Jul 14 '13 at 19:43
    
@ypercube: You are right –  porton Jul 15 '13 at 11:03
    
Added missing groupid=x.groupid AND –  porton Jul 17 '13 at 15:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.