Query within a Query

[meOmy]

Hi,

 

I have a table `assessments` as shown below

 

id     date                    username               provider

------------------------------------------------------------------------------------------

1     2015-09-15          me1@me.com         provider_1_name@me.com

2     2016-03-15          me1@me.com         provider_1_name@me.com

3     2016-09-07          me1@me.com         provider_1_name@me.com

4     2015-09-07          me2@me.com         provider_2_name@me.com

5     2016-03-07          me2@me.com         provider_2_name@me.com

6     2016-09-07          me2@me.com         provider_2_name@me.com

 

So, imagine provider 1 & 2 are high school counselors who need to be reminded to counsel a student 6 months after the last time the student was counseled.

 

So, what I want/need to figure out is how can I query all the records for dates that are beyond 6 months old? I did it like this:

 

$query = "SELECT date, username, provider FROM assessments WHERE date <= CURDATE() - INTERVAL 6 MONTH";

$results = mysqli_query($con, $query) or die (mysqli_error($con));

     

     while ($row = mysqli_fetch_array($results)) {

 

     $provider = $row['provider'];  // NOW I HAVE COUNSELOR'S EMAIL FOR OTHER QUERY

     $students = $row['username'];  // NOW I HAVE STUDENT'S EMAIL FOR OTHER QUERY

 

Works great!

 

However, over time every record will be older than 6 months and a reminder would have been sent at the appropriate time, so I only want to get the last record for EACH username, so I can send an email to the counselor letting them know it's time for another assessment just as it hits 6 months. In the table above that means I only want ID 3 and ID 6.

 

I can get the last record like this:

 

$query = "SELECT DISTINCT username FROM assessments WHERE username = '".$students."'

ORDER BY date DESC

LIMIT 1";

 

$results = mysqli_query($con, $query) or die (mysqli_error($con));

 

Problem is... I don't know how to put these two queries together. I need the first query to get the last record for each username that is older than 6 months and THEN the second query to the the username and provider for each of the results from the first query. Can anyone help me with this?

 

[requinix]

There are a few ways you can do this. Take a look at each of them, try them out, and see which makes the most sense to you and which has the best performance for your database.

 

Two versions of two versions: subquery vs. no query and JOIN vs. HAVING.

Subquery + JOIN

SELECT sub.date, a.username, a.provider
FROM assessments a
JOIN (
	SELECT MAX(date) AS date, username
	FROM assessments
	GROUP BY username
) sub ON a.username = sub.username
WHERE sub.date <= CURDATE() - INTERVAL 6 MONTH


Subquery + HAVING

SELECT (SELECT MAX(date) FROM assessments a2 WHERE a2.username = a1.username) AS date, a1.username, a1.provider
FROM assessments a1
HAVING date <= CURDATE() - INTERVAL 6 MONTH


No query + JOIN

SELECT a1.date, a1.username, a1.provider
FROM assessments a1
LEFT JOIN assessments a2 ON a1.username = a2.username AND a1.date < a2.date
WHERE a2.username IS NULL AND a1.date <= CURDATE() - INTERVAL 6 MONTH


No query + HAVING

SELECT MAX(date) AS date, username, provider
FROM assessments
GROUP BY username
HAVING date <= CURDATE() - INTERVAL 6 MONTH