[carters-site]

I could not come up with a very good SQL statement maybe someone else can.

Here is a mySQL solution...(I think will work I didn't not bother to test.)
Basically I built a function that you can test the result against on a per row basis to see if the current row year has another row in the able that matches the rep and course and is also only 1 year difference. As long as that result is 1 or greater then you should keep that row.

Code:

CREATE FUNCTION getYearcount(course VARCHAR(100), rep_id INT, YEAR INT)
RETURNS INT
READS SQL DATA

DETERMINISTIC
  BEGIN
  
  DECLARE numRep;
  
     SET @tmpCourse = course;
     SET @tmpRep_id = rep_id; 
     SET @tmpYear = year;
     
     SELECT count(rep_id) FROM course_reps WHERE course=@tmpCourse AND rep_id = @tmpRep_id 
     AND (year = @tmpYear - 1 OR year = @tmpYear + 1) 
     INTO numRep; 
     
     RETURN numRep;
  END

Your sql would be something like this.

Code:

SELECT rep_id FROM reps WHERE getYearcount(course, rep_id, year) > 0;

Should only return rows with the results you want... This is in theory and untested code. Hopefully that helps you out. Oh by the way this needs MySQL 5 with function support.