[landing]

Hi there

I'm trying to establish if the following is possible using SQL on it's own (I know it can be done by means of scripting)

I have the following table

year (int)
course (string)
rep_id (int)

The table describes "course representitives" in a particular year. I'm trying to find a way to list the rep_id of all reps who have been a rep in 2 consecutive years. I can't for the life of me establish the SQL.

Hope someone can help.


Thanks

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

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.

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.