SQL query to include an expiration date that falls in a range, but then exclude if they have another instance of the same type

Question

Here's the issue in verbose mode. I have instances of certifications on a certification table and they all have a type and a student associated with them. Here's what I want to happen, I want to pull all certs with an expiration that falls within a date range (NOW to 1 year). If they qualify for that parameter, great but then I want to exclude the student if they have a cert expiration that's greater than the range when it's the same type as the cert that falls within the range -- they don't need to be in the report. Here's the first query that I have:

SELECT s.student_id, s.fname, s.address1, s.lname, s.zip, s.state, s.city, s.student_type
          FROM students s, certifications c
          WHERE ( s.student_id = c.student_id )
            AND s.status='A' 
            AND s.student_type != 'B' 
            AND s.student_type != 'D'  
            AND s.student_type != 'E'
            AND s.student_type != 'W'  
            AND s.student_type != 'T'  
            AND s.student_type != 'I'  
            AND c.expiration >= CURDATE() AND c.expiration <= DATE_ADD(NOW(), INTERVAL 1 YEAR)
          GROUP BY s.student_id
             ORDER BY s.lname, s.fname

Then the sql that actually get's the cert's info based on the previous sql statement:

SELECT c.cert_num, c.date, expiration, ct.name, ct.cert_type, c.cert_id, c.student_id
              FROM  certifications c, cert_type ct 
              WHERE student_id = '{$Row['student_id']}'
              AND ct.cert_type = c.cert_type
              ORDER BY ct.name ASC, expiration DESC

So to summarize, the problem that I'm running into is that students show up if their cert's expiration falls within a year from now, and if they have another cert of the same type that has an expiration greater than the range. That's no good.

Is there a way to check to make sure a certain cert type falls within the range of dates and if so, then make sure they don't have a cert of the same type that is greater than the range? It don't care if it takes another sql query.

Answer 1

It is possible I am over simplifying the problem, but can you not just get the maximum expiration date for any student/type combination? e.g.

SELECT  s.student_id, 
        s.fname, 
        s.address1, 
        s.lname, 
        s.zip, 
        s.state, 
        s.city, 
        s.student_type
FROM    Students s
        INNER JOIN 
        (   SELECT  c.student_ID, 
                    c.Cert_Type, 
                    MAX(Expiration) AS Expiration
            FROM    Certifications c
            GROUP BY c.student_ID, c.Cert_Type
        ) c
            ON s.Student_ID = c.Student_ID
WHERE   s.Student_Type NOT IN ('B', 'D', 'E', 'W', 'T', 'I')
AND     c.expiration >= CURDATE() AND c.expiration <= DATE_ADD(NOW(), INTERVAL 1 YEAR)
GROUP BY s.student_id, s.fname, s.address1, s.lname, s.zip, s.state, s.city, s.student_type

Answer 2

Using a subquery:

SELECT s.student_id, s.fname, s.address1, s.lname, s.zip, s.state, s.city, s.student_type
  FROM students s, certifications c
  WHERE ( s.student_id = c.student_id )
    AND s.status='A' 
    AND s.student_type != 'B' 
    AND s.student_type != 'D'  
    AND s.student_type != 'E'
    AND s.student_type != 'W'  
    AND s.student_type != 'T'  
    AND s.student_type != 'I'  
    AND c.expiration >= CURDATE() AND c.expiration <= DATE_ADD(NOW(), INTERVAL 1 YEAR)
    AND NOT EXISTS (
      SELECT c_inner.id 
      FROM certifications c_inner 
      WHERE c.expiration > DATE_ADD(NOW(), INTERVAL 1 YEAR)
        AND c.student_id = c_inner.student_id
    )
  GROUP BY s.student_id
    ORDER BY s.lname, s.fname

I think it should be possible to refactor this into a join, which could give better performance.