MySQL query from PHP innerjoin and MAX(dates)

[ronmegga]

I am trying to create a query in MySQL with PHP. I have 2 tables , client_info and activity. The user will input 2 dates creating a range(to and from). I am trying to get the most recent activity.next_appt_date for a client, compare it to the date range and if a client's most recent activity.next_appt_date is with in the range given, then return that client's info as well at the activity.next_appt_date. i cant seem to get the query to work correctly, here is what I have so far...

$appt_sql = "SELECT a.ssn,MAX(a.date_next_appt),c.fname,c.lname,c.ssn FROM activity as a INNER JOIN client_info as c ON a.ssn=c.ssn WHERE (MAX(a.date_next_appt) > '$date_from' AND MAX(a.date_next_appt) < '$date_to') GROUP BY c.fname,c.lname,c.ssn";

the reason I need the most recent date being with in the date range is that there may be several entries for a client and there may be a more recent entry for that client so if they have a activity.next_appt_date with in the range but isnt that client's most recent, I dont want that client returned. This may be simple but I am not very familiar with complex MySql queries. Any help would be greatly appreciated.

[coopster]

Welcome to WebmasterWorld, ronmegga!

How about using BETWEEN [dev.mysql.com] and LIMIT [dev.mysql.com]?

$ssn = 1234567890; 
$appt_sql = "SELECT 
 a.ssn, 
 a.date_next_appt, 
 c.fname, 
 c.lname, 
 c.ssn 
 FROM activity AS a 
 INNER JOIN client_info AS c 
 ON a.ssn = c.ssn 
 WHERE a.ssn = $ssn 
 AND a.date_next_appt BETWEEN '$date_from' AND '$date_to' 
 ORDER BY a.ssn, a.date_next_appt 
 LIMIT 1 
";

[ronmegga]

well..for one thing, there is no specified ssn by the user. So I am not sure this will work. I tried without the line WHERE c.ssn=$ssn and it didnt work.

[coopster]

The whole '$ssn' variable was hypothetical, you need to specify how you identify the client. You said ...

I am trying to get the most recent activity.next_appt_date for a client

... you need to tell us how you identify the client.

[ronmegga]

I am trying to find clients with their most recent activity.next_appt_date within the range selected. Once that is found, take the activity.ssn in that row and join it with the matching client_info.ssn for the rest of the client_info.* fields. There is no preselected client. I am trying to search for appointment dates that are in a range for a report. For example...if the dates selected were $date_from='2004-10-01', $date_to='2004-10-07'

client_info table:
fname:John lname:Doe ssn:12345
fname:Joe lname:Jenkins ssn:23451

activity table:
ssn:12345 next_appt_date:2004-10-01
ssn:12345 next_appt_date:2004-10-08
ssn:23451 next_appt_date:2004-10-01
ssn:23451 next_appt_date:2004-10-06

row returned within range i gave above:
fname:Joe lname:Jenkins ssn:23451 next_appt_date:2004-10-06

note:John Doe wasnt returned because his max next_appt_date was '2004-10-08' and didnt fall into the range.

[coopster]

Actually, ranges are inclusive, therefore the 2004-10-01 date would be included and John Doe's next appointment would have been returned as 2004-10-01. However, you can tweak it to your liking. Your second explanation with some sample data clears things up.

SELECT a.ssn, MAX(a.next_appt_date), c.fname, c.lname FROM activity AS a INNER JOIN client_info AS c ON a.ssn = c.ssn WHERE a.next_appt_date BETWEEN '$date_from' AND '$date_to' GROUP BY a.ssn, c.fname, c.lname ORDER BY c.fname, c.lname, a.ssn;

You must include columns in your GROUP BY that are part of the SELECT column list. Also, I'm not sure you want MAX here, you may actually want MIN.

[ronmegga]

For some reason this query isnt returning any results. I have input a few entries and selected the ranges necessary to retrieve them and nothing is returned.

[ronmegga]

For some reason this query isnt returning any results. I have input a few entries and selected the ranges necessary to retrieve them and nothing is returned.

[ronmegga]

I have figured it out, thank you for all of your help. Here is what I came up with and it works perfectly....

$appt_sql = "SELECT a.ssn, MAX(a.date_next_appt) as apptdate, c.fname, c.lname FROM activity AS a INNER JOIN client_info AS c ON a.ssn = c.ssn GROUP BY a.ssn, c.fname, c.lname HAVING MAX(a.date_next_appt) BETWEEN '$date_from' AND '$date_to'";