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

Question

Here the problem is in verbose mode. I have certificate instances in the certification table, and all of them are of type and student associated with them. Here, what I want, I want to pull out all the certificates with an expiration date that falls within the date range (NOW up to 1 year). If they have the right to this parameter, excellent, but then I want to exclude the student, if they have a certificate expiration greater than the range, when it is the same type as the certificate that falls into the range - they do not need to be in the Report . Here is the first query 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 sql, which actually gets certificate information 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 I am facing is that students find that the certificate expires in a year, and if they have another certificate of the same type that has an expiration date that exceeds the range. This is not good.

Is there a way to verify that a particular type of certificate falls within a date range, and if so, make sure they do not have a certificate of the same type that is larger than the range? It doesn't matter if it requires another sql query.

Answer 1

I may simplify the problem more, but can you not just get the maximum expiration date for any student / type combination? eg.

 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 reorganize this into a connection, which can give better performance.

Answer 3

 SELECT s.student_id, s.fname, s.address1, s.lname, s.zip, s.state, s.city, s.student_type FROM students s JOIN certifications c ON c.student_id = s.student_id LEFT JOIN certifications c2 ON c2.student_id = s.student_id AND c2.expiration > DATE_ADD(NOW(), INTERVAL 1 YEAR) JOIN cert_type ct ON ct.cert_type=c.cert_type 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 c2.student_id is null ORDER BY s.lname, s.fname