Jump to content

Query within a Query


meOmy

Recommended Posts

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?

 

Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.